Formatting Dollar Amounts

Mar 8th, 2002, 10:48 AM

What's the best way to remove the non-numeric characters ($ , .) from a dollar amount, whil preserving the actual dollar amount.
To be specific, I have a field which has dollar amounts of varying lengths (up to 12 characters), which need to be formatted so that just the numbers remain.
The problem I've been running into, is the standard format commands either round the amount up and/or drop the last digit when it is a zero.
For example, I need $156.80 to appear as 15680, and not 157 or 1568.

Thanks.

Mark W. · Mar 8th, 2002, 10:59 AM

This can't be done with formatting. Formatting doesn't alter the internal representation of values. If A1 contains 156.8 formatted as $156.80 you could get your desired value using =A1*100

[ This Message was edited by: Mark W. on 2002-03-08 10:01 ]

Tim Francis-Wright · Mar 8th, 2002, 12:13 PM

If all you have is dolalrs and cents, then
you could try

=TEXT(A1*100,"#00")

to get what you want.

Mar 8th, 2002, 12:13 PM	#3
Tim Francis-Wright Board Regular Join Date: Feb 2002 Posts: 74	If all you have is dolalrs and cents, then you could try =TEXT(A1*100,"#00") to get what you want. __________________ "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 8th, 2002, 10:48 AM	#1
Guest Posts: n/a	What's the best way to remove the non-numeric characters ($ , .) from a dollar amount, whil preserving the actual dollar amount. To be specific, I have a field which has dollar amounts of varying lengths (up to 12 characters), which need to be formatted so that just the numbers remain. The problem I've been running into, is the standard format commands either round the amount up and/or drop the last digit when it is a zero. For example, I need $156.80 to appear as 15680, and not 157 or 1568. Thanks.

Mar 8th, 2002, 10:59 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	This can't be done with formatting. Formatting doesn't alter the internal representation of values. If A1 contains 156.8 formatted as $156.80 you could get your desired value using =A1*100 [ This Message was edited by: Mark W. on 2002-03-08 10:01 ]