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
522 Views

Similar Articles

[PageSpeed] 23

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:

want questions
Hello Everyone, I want some set of questions on Word-2007 to check competency level of the user. Please help. -- Ananta Gupta Ananta, please refer to my other 2 postings: just check out the WORD part. If my comments have helped please hit Yes. Thanks. "Ananta Gupta" wrote: > Hello Everyone, > > I want some set of questions on Word-2007 to check competency level of the > user. Please help. > > -- > Ananta Gupta "trip_to_tokyo" wrote: > Ananta, please refer to my other 2 postings: just check out the WOR...

Unable to receive e-mail message formatted in RTF
I'm using Outlook 2003/SP2 on Windows/2000 connecting to an Exchange Server. When someone sends a message in RTF, what I get is a message that includes both HTML and plain text, but no RTF. I've gone all through the e-mail and account options in Outlook and have found some settings which allow me to send in RTF, but none that would prevent me from viewing the message in RTF. Note that when I look at the source of the message, I see the two parts: Plain Text and HTML - no RTF to be found. It is as if the exchange server is converting the message somehow. Everyone here uses the same...

increment a cell value, and fix the value
I want to be able to have a "cell" value that can be incremented by say a doller value in another cell, but then have the "cell" value fixed. This is becuase if I then want to change the new "cell" value by different dollar amount, I want to be able to use an incremental dolla amount, and not a total dollar amount. Example: "cell" = $10 Input $2 into dollar cell, therfore "cell" changes to $12. If I then want to add an increase of $4 to make "cell" = $16, I want t be able to input $4 into the dollar cell instead of $6 -- Message p...

Conditional formatting #82
I am trying to do the following: 1. In cell H14, if the cell is blank, turn blue. 2. In cell H14, if the cell has a date less than or equal to another cell, turn green. 3. In Cell H14, if the cell has a date greater than another cell, turn orange. 4. If NA is placed in the cell, revert to no format. I have managed the first three, but cannot get the fourth one to work. What formulas should I be putting in Each condition? What happens when you put NA in the cell? -- Regards, Peo Sjoblom "ChristinaC" <ChristinaC@discussions.microsoft.com> wrote in message news:BD9...

I want more!
I need to extend a form that aleady has some 100 lines. For instance, one column has the date, but only until tomorroq. How do I create more lines based on the same formula? Thank you JB I usually select the last row in the range and copy it down a few rows. If you're using xl2003, you may want to look at Data|List. And if you're using xl2007, this feature has transformed into Tables. (Data tab on the ribbon|Data tools Group|What if Analysis|Data table) JB wrote: > > I need to extend a form that aleady has some 100 lines. For instance, one > column has the da...

Combing Cell Values
Hello, Need some help with combing cell values. I know that =value(a1&b1) example. Cell A1 = 1 Cell B1= 3 Output =13 But if A1 has a text value such as ABC, I get an error on output. With text and numeric, how do you combine? Thanks JR try leaving out value -- Don Guillett SalesAid Software donaldb@281.com "JR" <gaspower@nothing.sbcglobal.net> wrote in message news:mqWJb.5923$KV4.1988@newssvr29.news.prodigy.com... > Hello, > > Need some help with combing cell values. I know that =value(a1&b1) example. > > Cell A1 = 1 > Cell B1= 3 > > Ou...

Convert decimal degree (lattitude/longitude) into Degree,
Need formulae to convert decimal degree (lattitude/longitude) into Degree, Minutes, Seconds format. I have the following: Lattitude data of 0.1741463938 and to convert to decimal degrees, I muliply by 180 which gives me decimal degrees of 31.3463508840. Now I need to convert to a Degree, Minute, Seconds format (N34* 50' 56.04"). Note: the '*' represents the 'degree' symbol - did not know how to display the symbol. Need to also display whether 'N' (North) or 'S' (South) based on actual coordinate. For Longitude, I have data of -0.4758427618 *1...

I don't want a page of the same label, I want one of each...
I can't find where to tell Pubisher that I want one of each label and not a page of each label. I'm using Publisher 2003 (Office 2003) I see a post that says to enter "<<Next Record>>" but Publisher doesn't see this as a field, just as text. This can't be that hard, but I can't figure it out... Any help would be appreciated. Kelvin I see that it prints correctly, but the print preview displayes it wrong.... I think I have it figured out... "Kelvin" <someone@domain.com> wrote in message news:OSpLwThUIHA.5508@TK2MSFTNGP04.phx....

importing tables in proper format from word 2007 into access 2007
The word 2007(.docx) is saved as a plain text(.txt) file first. Then, I add a delimiter(#) to each row manually. The tables in the document look like below Table 1 Row 1 data# Row 2 data# Row 3 data# Row 4 data# .. .. .. Table 2 Row 1 data# Row 2 data# Row 3 data# .. .. .. .. Table 3 Row 1 data# Row 2 data# Row 3 data# .. .. .. and so on Then, go into Access 2007, External Data, Text File to import the text file data into Access choosing # as the delimiter The table which is created in Access 2007 is as follows Field1 Row 1 data of Table 1 ...

extracting data from a cell
I have this data that stored into several cells and each sell holds a combined 20 data entries in one cell seperated by a space. I was wondering is there anyway to extract that data with out having to rekey the numbers. We are talking about a combined 2000 line items if individually entered. An example is to store numbers 1-100 in to one Use Data | Text to Columns and use the space as a delimiter/separator. Regards Trevor "msam137" <mpsamuels01@spymac.com> wrote in message news:1109702417.132521.302110@z14g2000cwz.googlegroups.com... >I have this data that stored into...

how do I populate a cell with data from another worksheet,
I have two worksheets, both with similar data. Say columns are Acc No, Acc Name and VAT number. Unfortunately the VAT number does not correspond on each worksheet. How can I populate 1 worksheet with the VAT number so that the data is collected on one worksheet? I have tried VLookup, but I'm not really converant with it so would appreciate any help. Many thanks Denise ...

Text want wrap in text box
I can't get the text to wrap in a text box, word wrap is set to true, I put some text boxes on a sheet today and the text will not wrap, I did this yesterday and it worked fine, I can copy the ones from yesterday and paste them on a new sheet and they work, any ideas? I have rebooted but this did not help. Using Excel 2000 (9.0.6926 SP3) Thanks Paul B If you're referring to a Control Toolbat text box make sure that the MultiLine property is True. -- Jim Rech Excel MVP "Paul B" <pbridgesnews@uga.edu> wrote in message news:%23ovLUkuLEHA.2456@TK2MSFTNGP12.phx.gbl......

Adding text in cells
I would like to add text to a cell just like using word. That is, being able to type a short line and hit return for the next line. Right now I can not have separate lines of text. I would like it to look something like this. The 1st stop is Dallas. Go to the top of the hill and turn right at the tee. Go 14 miles to the light and turn right again. Call me at 12:00 PM. And can parts of the text be bold or have color? Thanks, Art Type first line then hit Alt + ENTER to give a linefeed within the cell. Yes, you can boild and color text within a cell. In the formula bar select the part(s)...

Conditional Format if not formula
I wish to use Conditional Formatting to highlight a cell if a formula is overwritten by a manual number. The cells typically have formulas but there are times a manual number must be inserted in the cell, it is then I want the cell to be highlighted. Is there a formula that can be used in Conditional Formatting that will do this? Thanks for your time, Gary Gary, You could use the change event: copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. You can either remove or modify this line If Intersect(Target, ...

Formatting formulas
I'm entering a formula into a cell. As I enter the formula, Excel is providing the normal cues, i.e., telling me which argument I should be entering (all well and fine). However, once I'm done, it appears that Excel is interpreting what I entered as text. If I edit the cell, all looks fine. If I check the Format of the cell, it says it's a text. What am I doing wrong? .. . .and the formula is . . . .? -- Russell Dawson Excel Student "jmt" wrote: > I'm entering a formula into a cell. As I enter the formula, Excel is > providing the...

Go to cell based on input
Hi all In hoping someone can help I have a cell (c16) the user will input a 1 or a zero into. If its a 1 i want cell (F16) to be selected or if its a 0 cell (C17) to be selected. Cheers in Advance D -- Message posted via http://www.officekb.com Put this code in the worksheet module. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$16" Then Select Case Target.Value Case Is = "0": Range("C17").Select Case Is = "1": Range("F16").Select End Se...

Want to Add more Sub description Field
How I can add more sub description field like 1,2,3,4,5 regards Saleem Suri Salaam Saleem, You have only limit up to 3 fields in RMS 1,2,3 sub-description also you can use the extended description and there is main description for the item. so Altogether you have upto 5 description limit and I think that is enough. Let me know if this won't fulfill your requirements else use the Item Notes for more description. Rate if like Regards Akber "Saleem Suri" wrote: > How I can add more sub description field like 1,2,3,4,5 > > regards > Saleem Suri Salam Akbar ...

printing workbook -landscap in to portrait format in Excel
One of my financial program generates Excel workbook (with about 10 worksheets). they all show up in Landscap format. When printing I would like to print in Portrait format. Is there an easy way to print entire workbook in Portrait without changing each worksheet format? Select all sheets (click first tab, hold down shift and click last tab), do the page setup, then ungroup the sheets Regards, Peo Sjoblom "rjoshi" wrote: > One of my financial program generates Excel workbook (with about 10 > worksheets). they all show up in Landscap format. When printing I would li...

Text entered in two separate cells appear as one word in another
Text entered in two separate cells eg. A1 TOM B1 HARRIS how do I get these two words to appear as TOMHARRIS in Cell C1 Hi, =A1&B1 if you need a comma in between =A1&","&B1 if this helps please click yes, thanks "lizzie" wrote: > Text entered in two separate cells eg. A1 TOM B1 HARRIS how do I get these > two words to appear as TOMHARRIS in Cell C1 ...

PM check numbering
Other than the user overriding the check number for a payables check run, is there a reason why a check number might be skipped? Thanks, M. M, When you go to enter a Manual Payment transaction, the default Payment Method is Check...if someone is recording a wire payment, but doesn't change the Document Number or the Payment Method, then the next available check number is used by this transaction even though you have not printed a check. Similarly, when you enter a Bank Transaction, the default Type is Check and if you don't change that or the Number, this transaction will u...

Programming without XPathNavigator.SelectSingleNode (framework 1.1)
Hi, given the following simple XML fragment: <country> <code>ES</code> <description>SPAIN</description> <destination> <code>IBZ</code> <description>IBIZA</description> </destination> <destination> <code>PMI</code> <description>MALLORCA</description> </destination> <destination> <code>MAH</code> <description>MENORCA</description> </destination> </country> <country> <code>PT</code> <description>PORTUGAL</...

I want to add a sound event when the cursor bumps left margin
I want to know (via an event sound) that my cursor movement has bumped the left edge of the spreadsheet. I am entering thousand of rows of data, and it would help if there was a event sound when I returned the cursor to the left edge. I have this feature in 123, and find it helpfull. Put this in the sheet module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static ColOld As Integer If Target.Column = 1 And ColOld > 1 Then Beep ColOld = Target.Column End Sub -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "m...

catalog merge formats
Ok my problem is that I am trying to put a lot of data through a catalog merge. I will have16 discrete data and photo merge links on a page. I want to have it drop into a format that I will not have to realign and futz with after the merge. It appears the only way to do this is to do the first catalog merge layout and the software decides what is best (ie how many to a page where to put them on the page, etc). Is there anyway to put the multiple catalog merge formats on the master page and then do the data link function??? Please help. You can adjust the merge area to as few or many entries...

I can't see formatting on my screen for font colors or highlighti.
Why can't I see formatting on my screen for font colors and filling cells with color? Try this OFF: Changes to Fill Color and Fill Pattern Are Not Displaye�d http://support.microsoft.com/default.aspx?scid=kb;en-us;320531 -- Regards Ron de Bruin http://www.rondebruin.nl "kam347" <kam347@discussions.microsoft.com> wrote in message news:492EDFB8-6199-49E5-9996-F9DE60CE9243@microsoft.com... > Why can't I see formatting on my screen for font colors and filling cells > with color? ...

two page numbers per "page."
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I would like to make a book pages... like a novel: One sheet of paper, sideways, with two columns of text (and TWO PAGE NUMBERS). You fold the pages over to make it book size. <br><br>I haven't been able to make the two columns each have a separate page number (except typing each one in). Thought maybe &quot;Sections&quot; but couldn't make it work. Looking for suggestions. Thanks. Hi, it would be possible to do so with section breaks, but it would be extremely tedious, as you woul...