I want to format a number cell without the decimal and without ro.

I need to format the number cell so that it will still show the numbers after 
the decimal, but not show the decimal point at all.  It must also display 
leading zeros, which is not a problem.  I just need a way to strip the 
decimal from the number.
0
LAM (33)
4/18/2005 2:53:02 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
665 Views

Similar Articles

[PageSpeed] 46

If if needs to stay a number with the same value, I don't think you can do it.
If you can change the value Just multiple the number by ten to the number of 
decimal places.
If it can be text set up a new column and use 
=substitute(a1,".',"")

"LAM" wrote:

> I need to format the number cell so that it will still show the numbers after 
> the decimal, but not show the decimal point at all.  It must also display 
> leading zeros, which is not a problem.  I just need a way to strip the 
> decimal from the number.
0
BJ (832)
4/18/2005 3:13:10 PM
The column needs to be formatted numeric.  This is a file that will be 
transmitted to the bank to verify Payables check amounts.  It is called an 
ARP Standard Issue Input Format.  If it is "Standard" I wonder why it is so 
difficult to do?  I am creating a .csv file from our Payables system with all 
of the information needed.  It is formatting it for the ARP Standard that is 
giving me fits.


"bj" wrote:

> If if needs to stay a number with the same value, I don't think you can do it.
> If you can change the value Just multiple the number by ten to the number of 
> decimal places.
> If it can be text set up a new column and use 
> =substitute(a1,".',"")
> 
> "LAM" wrote:
> 
> > I need to format the number cell so that it will still show the numbers after 
> > the decimal, but not show the decimal point at all.  It must also display 
> > leading zeros, which is not a problem.  I just need a way to strip the 
> > decimal from the number.
0
LAM (33)
4/18/2005 3:22:01 PM
On Mon, 18 Apr 2005 08:22:01 -0700, LAM <LAM@discussions.microsoft.com> wrote:

>The column needs to be formatted numeric.  This is a file that will be 
>transmitted to the bank to verify Payables check amounts.  It is called an 
>ARP Standard Issue Input Format.  If it is "Standard" I wonder why it is so 
>difficult to do?  I am creating a .csv file from our Payables system with all 
>of the information needed.  It is formatting it for the ARP Standard that is 
>giving me fits.
>
>

I don't know what that format is.

If you were in the US, then possibly this format is merely dollars and cents,
without the decimal.

If that is the case your original numbers should have two decimal precision.

Just multiply the numbers by 100.

If some of these values are the result of computations, ROUND them to two
decimal first.

e.g.  =100*ROUND(A1,2)

How to do this most easily depends on more information than you have posted.


--ron
0
ronrosenfeld (3122)
4/18/2005 5:16:19 PM
I also am not familiar with that format

"Ron Rosenfeld" wrote:

> On Mon, 18 Apr 2005 08:22:01 -0700, LAM <LAM@discussions.microsoft.com> wrote:
> 
> >The column needs to be formatted numeric.  This is a file that will be 
> >transmitted to the bank to verify Payables check amounts.  It is called an 
> >ARP Standard Issue Input Format.  If it is "Standard" I wonder why it is so 
> >difficult to do?  I am creating a .csv file from our Payables system with all 
> >of the information needed.  It is formatting it for the ARP Standard that is 
> >giving me fits.
> >
> >
> 
> I don't know what that format is.
> 
> If you were in the US, then possibly this format is merely dollars and cents,
> without the decimal.
> 
> If that is the case your original numbers should have two decimal precision.
> 
> Just multiply the numbers by 100.
> 
> If some of these values are the result of computations, ROUND them to two
> decimal first.
> 
> e.g.  =100*ROUND(A1,2)
> 
> How to do this most easily depends on more information than you have posted.
> 
> 
> --ron
> 
0
BJ (832)
4/18/2005 5:29:02 PM
For example, my input data file brings in the amount  72208.88.  For the 
submission file this entry needs to appear 000007220888
The decimal is implied and the amount needs to be right justified and zero 
filled, with the column width at 12. 

"bj" wrote:

> I also am not familiar with that format
> 
> "Ron Rosenfeld" wrote:
> 
> > On Mon, 18 Apr 2005 08:22:01 -0700, LAM <LAM@discussions.microsoft.com> wrote:
> > 
> > >The column needs to be formatted numeric.  This is a file that will be 
> > >transmitted to the bank to verify Payables check amounts.  It is called an 
> > >ARP Standard Issue Input Format.  If it is "Standard" I wonder why it is so 
> > >difficult to do?  I am creating a .csv file from our Payables system with all 
> > >of the information needed.  It is formatting it for the ARP Standard that is 
> > >giving me fits.
> > >
> > >
> > 
> > I don't know what that format is.
> > 
> > If you were in the US, then possibly this format is merely dollars and cents,
> > without the decimal.
> > 
> > If that is the case your original numbers should have two decimal precision.
> > 
> > Just multiply the numbers by 100.
> > 
> > If some of these values are the result of computations, ROUND them to two
> > decimal first.
> > 
> > e.g.  =100*ROUND(A1,2)
> > 
> > How to do this most easily depends on more information than you have posted.
> > 
> > 
> > --ron
> > 
0
LAM (33)
4/18/2005 6:37:03 PM
On Mon, 18 Apr 2005 11:37:03 -0700, LAM <LAM@discussions.microsoft.com> wrote:

>For example, my input data file brings in the amount  72208.88.  For the 
>submission file this entry needs to appear 000007220888
>The decimal is implied and the amount needs to be right justified and zero 
>filled, with the column width at 12. 

Since you are exporting it as a CSV file with a fixed (12) number of spaces in
the column, justification should be irrelevant.

To convert your numbers:

=TEXT(ROUND(A1*100,2),"000000000000")

Then copy/paste special values back over the original numbers.


--ron
0
ronrosenfeld (3122)
4/18/2005 9:35:43 PM
It sounds as though multiplying by 100 and using a <format><cells><custom> 
type [0000000000000] should give you the output you need

"LAM" wrote:

> For example, my input data file brings in the amount  72208.88.  For the 
> submission file this entry needs to appear 000007220888
> The decimal is implied and the amount needs to be right justified and zero 
> filled, with the column width at 12. 
> 
> "bj" wrote:
> 
> > I also am not familiar with that format
> > 
> > "Ron Rosenfeld" wrote:
> > 
> > > On Mon, 18 Apr 2005 08:22:01 -0700, LAM <LAM@discussions.microsoft.com> wrote:
> > > 
> > > >The column needs to be formatted numeric.  This is a file that will be 
> > > >transmitted to the bank to verify Payables check amounts.  It is called an 
> > > >ARP Standard Issue Input Format.  If it is "Standard" I wonder why it is so 
> > > >difficult to do?  I am creating a .csv file from our Payables system with all 
> > > >of the information needed.  It is formatting it for the ARP Standard that is 
> > > >giving me fits.
> > > >
> > > >
> > > 
> > > I don't know what that format is.
> > > 
> > > If you were in the US, then possibly this format is merely dollars and cents,
> > > without the decimal.
> > > 
> > > If that is the case your original numbers should have two decimal precision.
> > > 
> > > Just multiply the numbers by 100.
> > > 
> > > If some of these values are the result of computations, ROUND them to two
> > > decimal first.
> > > 
> > > e.g.  =100*ROUND(A1,2)
> > > 
> > > How to do this most easily depends on more information than you have posted.
> > > 
> > > 
> > > --ron
> > > 
0
BJ (832)
4/18/2005 9:45:13 PM
Thanks, bj
THat works like a charm!

"bj" wrote:

> It sounds as though multiplying by 100 and using a <format><cells><custom> 
> type [0000000000000] should give you the output you need
> 
> "LAM" wrote:
> 
> > For example, my input data file brings in the amount  72208.88.  For the 
> > submission file this entry needs to appear 000007220888
> > The decimal is implied and the amount needs to be right justified and zero 
> > filled, with the column width at 12. 
> > 
> > "bj" wrote:
> > 
> > > I also am not familiar with that format
> > > 
> > > "Ron Rosenfeld" wrote:
> > > 
> > > > On Mon, 18 Apr 2005 08:22:01 -0700, LAM <LAM@discussions.microsoft.com> wrote:
> > > > 
> > > > >The column needs to be formatted numeric.  This is a file that will be 
> > > > >transmitted to the bank to verify Payables check amounts.  It is called an 
> > > > >ARP Standard Issue Input Format.  If it is "Standard" I wonder why it is so 
> > > > >difficult to do?  I am creating a .csv file from our Payables system with all 
> > > > >of the information needed.  It is formatting it for the ARP Standard that is 
> > > > >giving me fits.
> > > > >
> > > > >
> > > > 
> > > > I don't know what that format is.
> > > > 
> > > > If you were in the US, then possibly this format is merely dollars and cents,
> > > > without the decimal.
> > > > 
> > > > If that is the case your original numbers should have two decimal precision.
> > > > 
> > > > Just multiply the numbers by 100.
> > > > 
> > > > If some of these values are the result of computations, ROUND them to two
> > > > decimal first.
> > > > 
> > > > e.g.  =100*ROUND(A1,2)
> > > > 
> > > > How to do this most easily depends on more information than you have posted.
> > > > 
> > > > 
> > > > --ron
> > > > 
0
LAM (33)
4/20/2005 3:59:02 PM
Reply:

Similar Artilces:

Logon to OWA without domain name
Is there a way to setup my OWA so that I can type just the username, instead of domain\username? "Tim Kowal" <tim@nospam.com> wrote in message news:gz2Dc.2397$pT3.482@newssvr27.news.prodigy.com... > Is there a way to setup my OWA so that I can type just the username, instead > of domain\username? You need to set the Default Authentication Domain (click the button next to 'Basic Authentication') for the 'Exchange' Virtual Directory in IIS Manager. Lee. -- _______________________________________ Outlook Web Access For PDA , OWA For WAP: www.leederby...

What is the cell data reference for 5th row and 7th column called
Can someone help me with this question? I need to get the answer for my homework. Thanks for your help. I'm guessing, but might it be G5? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Shy-di@verizon.net" <Shy-di@verizon.net@discussions.microsoft.com> wrote in message news:A0979494-228B-435E-98F7-6BD26C900266@microsoft.com... > Can someone help me with this question? I nee...

language date formatting
My PC is regional setting is dutch I have to print documents with date format dd-mmm-yyyy Problem is, that mmm should always be English (not dutch). How to enforce that? The only way I can think of is to write a custom VBA function to return the month abbreviation. UNTESTED sample function with no error handling follows Public Function fEnglishDate (DateIn as Variant) Dim dateString as String If IsDate(DateIn) = False Then fEnglishDate = DateIn Else Select Case Month(DateIn) Case 1 : "Jan" Case 2: "Feb" ... ...

how do i see the numbers of pages at the bottom of my work sheet?
i used to see the page numbers at the bottom of the screen when i worked on a multi-page document, so i could easily move from page to page. now i have to go to edit- then go to page- which is very inconvenient. what happened maggiekins wrote: > i used to see the page numbers at the bottom of the screen when i > worked on a multi-page document, so i could easily move from page to > page. now i have to go to edit- then go to page- which is very > inconvenient. what happened ============================= View / Status Bar. -- ******John Inzer******** **MS Picture It! MVP** ...

Cell Contains Data.
How can I tell a cell that IF a cell contains data (numbers, text, whatever) do x or y. How can I identify data?? I don't want to tell the function to look for a specific number or text, just data, just something in the cell... =IF(A1<>"","Data","No Data") "GEM" <GEM@discussions.microsoft.com> wrote in message news:07916799-F312-4E89-A926-59356B74ED89@microsoft.com... > How can I tell a cell that IF a cell contains data (numbers, text, > whatever) > do x or y. How can I identify data?? I don't want to tell the function ...

Active cell highlight
Hi, I would like to know if there was a way to have an active cell always in yellow or any color. Example: If the active cell is moved around, the active cell will be always in yellow. Thank you in advance maybe.... you may want to try Chip Pearson's Rowliner: http://www.cpearson.com/excel/RowLiner.htm E wrote: > > Hi, > > I would like to know if there was a way to have an active cell always in > yellow or any color. > > Example: If the active cell is moved around, the active cell will be always > in yellow. > > Thank you in advance -- Dave Pet...

Need to separate multiple numbers in one cell
I have multiple number in one cell and i need to somehow put each number into one separate cell for each number: I have : column A row1 2 5 8 10 I want: column B column C column D column E row1 2 5 8 10 How do I do it on Excel 2007? thanks EggHeadCafe - Software Developer Portal of Choice Map Stored Procedure Output To Class Properties http://www.eggheadcafe.com/tutorials/aspnet/2853a1aa-5db7-40d0-9cde-46847fa770ef/map-stored-procedure-outp.aspx Hi, Use Data > Text to columns and specify the delimiter as space ...

copy value in cell above to cell below for a whole column
I have a spreadsheet that was saved from a report. this report only lists the employee name once. There is a way to copy the value of the cell above to the cell below. I can get as far as highlighting the blank cells with the Go To function, then I can not remember the steps to tell excel to copy the data into the blank cells. Can anyone help? Ctrl+D will copydown the value of the top cell to the highlighted cells below. "tarthur" wrote: > I have a spreadsheet that was saved from a report. this report only lists > the employee name once. There is a way to copy th...

How can I remove the previous lock cells and keep the new ones.
Could you please help me?... I locked and protected the cells A1:H20 in the sheet 1 two months ago. However, yesterday I wanted to unlock and unprotect these cells and locked and protected the cells A21:H40 instead. However, for some reason the cells A1:H20 are still lock and protect. How can I remove the previous lock cells (A1:H20) and keep the new cells (A21`:H40) only. Thanks. Maperalia In EXCEL 2007 take the following actions to unlock cells A1 to H20:- 1. Home / Cells group / Format / Unprotect Sheet / enter the password to Unprotect Sheet / OK / highlight cells A1 t...

Adding data from multiple cell and linking to a new worksheet
I have data in a sheet with names and amounts due. Like A 500 B 300 A 600 B 300 C 400 C 200 There is a second sheet named summary. I want the totals of each person to appear in the summary sheet. These should be linked cells and any change in data in sheet 1 should be updtaed automatically Create a PivotTable http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "Rashmi" <Rashmi@discussions.microsoft.com> wrote in message news:56DC0F3A-8D60-4FE2-B778-3A07790EFE8A@microsoft.com... >I have data in a sheet wi...

Validation Rule for IBAN Account number in MS Access Form field
Hi, In my table (and the form field linked to it) I have a field for the IBAN account number, which must be 2 Upper case characters A to Z, followed by 18 manadatory digits, and up to 28 total. So in my input mask I have this: >LL00\-0000\-0000\-0000\-0000\-9999\-9999;; and it works fine. But when the user enters less than 20 characters total (including the two letters at the start and at least 18 digits) Access gives a very unfriendly ''The value you entered isn't appropriate for the input mask '>LL00\-0000\-0000\-0000\-0000\-9999\-9999;;' specified for thi...

Move cells down to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7...

using data from another cell
I would like to type a name in one cell(1) then check in list (different column) if the same name is there, and if it is there get a value of another cell locate in the same row where. Can some one help me? Create your second list and use VLOOKUP as follows: Name is typed in: A1 List of names and values in: C1:D10 =VLOOKUP(A1,$C$1:$D$10,2,FALSE) "leo" <reani1996@hotmail.com> wrote in message news:OiO5fCTWDHA.2268@TK2MSFTNGP11.phx.gbl... > I would like to type a name in one cell(1) then check in list (different > column) if the same name is there, and if it is t...

convert text to predefined number in single column
Hi all, Greeting to all of you out there! I am a newbie to this forum and now I have a question here. I have a worksheet which contains thousands of records. Within a particular column (column "N") there are single character in the cells throughout the records (eg. "M", "S"). My question is how can I convert from "M" to "1" and "S" to "2" using formula? Kindly advise. Thanks and best regards. Eric --- Message posted from http://www.ExcelForum.com/ Use a help column, =LOOKUP(N1,{"M","S"},{1,2}...

How to add customer's totalSales without transaction?
Some customers transaction record are lost. I only have their totalSales. Now I want to add these customers to rms with their totalSales. How can I do it? Jerry wrote: > Some customers transaction record are lost. I only have their totalSales. Now > I want to add these customers to rms with their totalSales. How can I do it? > You can add the customers and their totalSales only from QS Administrator with SQL query.. example: UPDATE Customer SET TotalSales = <AMOUNT> WHERE AccountNumber = <CustomerAccountNumber> bye antonio ...

How to exclude cells from a datasource?
Hi, I have an XY chart in excel97 that uses a range of cells for the data source. Every now and then I would like to exclude a XYnode from the chart. Without having to reselt the datasource. Is there a value one can enter in the cell that exludes it from the graph (compare: putting text in a cell excludes it from the SUM() function). For example point X Y a 1 1 b 2 2 c 3 3 Gives a graph with two lines connecting the three points While point X Y a 1 1 b ? ? c 3 3 Should give a graph with one line connecting point a and c. Point b is left out. Or is the...

What must I do to get gridlines printed for both filled and empty cells?
Hi, What must I do to get gridlines printed for both filled and empty cells? I'm a teacher, and I'm trying to replicate and print, in Excel, an attendance book format, with a series of tiny squares next to each student's name. The printed form of this would allow me to register if a student were present or absent on a particular day. Hubert -- Hubert Earl, vendor of fine Jamaican art, coffee, etc. on eBay: http://www.stores.ebay.com/id=12295024&ssPageName=L2?refid=store; Fine Jablum Blue Mountain Coffee sales page: http://stores.ebay.com/Jamaican-Coffee-Art-and-More-Store_Jab...

Copying formatting of entire sheet
I made an Excel 2007 workbook which includes my monthly business income, parts bought, a logbook for car mileage dividing into personal and work and finally a financial end of year total constantly updated by the income or parts bought over the financial year. What I would like to know, please, is how to copy the entire logbook (12 sheets, 1 per month then 1 for end of financial year totals and 1 for car miles) FORMATTING so that I can have it all ready for the next financial year. I live in Australia and our financial year ends Jun 30 and starts the new one July 1. What I would like t...

Assigning records to a user without a role
Hello people, This might have been mentioned before, but just to make sure hereby the following suggestion for a next release of CRM 4: When you assign a single record to a user that has no roles, you get a correct error message saying this. When you assing more than one record at the same time to this same user, you get a message simply saying that an error has occured and that one or more records have not been transferred, with no extra eplanation. This error message could be much better (just spend 10 minutes figuring out what this might have been - the error message for one messag...

After a hard drive format
Before I format my hard drive (it's time)what files do I need to backup to retain my emails and email addresses? Where do I find them? Thanks, Marvin If you are using Outlook 2000/2002, search for files with the extension ".pst". The exact location will depend on what operation system you are using. Regards Darren PS within your newsgroups settings, change your e-mail address to something like marskyNOSPAM@mindspring.com. Newsgroups are where spammers gain their e-mail addresses from by using "spiders" or "web-bots" to gain details of such addresses. ...

Excel Cell Formatting #3
How do I centeer a picture in an Excel cell? Hi you can't as such objects 'float' above the cells in Excel -- Regards Frank Kabel Frankfurt, Germany "Tom Coffey" <Tom Coffey@discussions.microsoft.com> schrieb im Newsbeitrag news:8E7356E1-4318-42F3-B9C6-3D7EDFBBEEA1@microsoft.com... > How do I centeer a picture in an Excel cell? ...

Count the number of worksheets in a workbook
Hello: Is there any function or other method to count the number of worksheets in a workbook? I have a workbook which includes dozens of worksheets (each for one client) and I would like to know the total number of clients. Thanks in advance! with VBA Function WSNum() WSNum = Activeworkbook.Worksheets.Count End Function -- HTH RP (remove nothere from the email address if mailing direct) "Vincdc" <Vincdc@discussions.microsoft.com> wrote in message news:DA1A3F0D-947F-44B7-B5A0-B28671216EC9@microsoft.com... > Hello: > Is there any function or other method to co...

Chart changing based on change in data source (number of rows/colu
As I see, the data range for charting is normally fixed. I have to produce chart based on chaning rows and/or columns in data source table. Has anybody got some suggestion for me? Bal Ram Bhui - For Excel charts in general, I suggest Jon Peltier's web site: http://www.peltiertech.com/ For dynamic charts, I suggest: http://www.peltiertech.com/Excel/Charts/Dynamics.html - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "Bal Ram Bhui, Jakarta" <Bal Ram Bhui, Jakarta@discussions.microsoft.com> wrote in message news:DD0B5564-26B0-4...

Bullets and Numbering
I am trying to create a template in Publisher for which i want to customise bullets as user define pictures. I am not able to do so. Is there any way out for this? Format copying from Word document doesnt seem to work also. Might try finding a dingbat font that suits you. Scaling down an image is an option. Bullets are fonts. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Lavanya" <Lavanya@discussions.microsoft.com> wrote in message news:FF3C5721-0A9A-4DA6-AB3D-B64BCAD6E3D1@microsoft.com... >I am trying to create...

Cell possibilities?
Hi, I have a spreadsheet which has dates when people have attended a course. The sheet then updates itself through conditional formatting and formulas to let me know when course dates have run out. Is it possible through using either a formula or conditional formatting that after a certain time period e.g. two months after a course date has run out, that the date in the cell is erased? If so how id it done? Thanks in advance. What formulas and conditional formulas are you presently using to determine when course dates expire? And what exactly do you mean by "erased"? Do you me...