Formatting

P

Penny

I have a column of data, which is formatted in currency. I
would like to be able to change $45,000,000.00 to say 45.0
without re-typing the information. Is there a way to do
this?

Thanks in advance

Penny
 
M

Mark Graesser

Hi Penny
You can apply a custom number format of #.0,

Each comma will knock off three zeros

Good Luck
Mark Graesse
[email protected]
Boston M

----- Penny wrote: ----

I have a column of data, which is formatted in currency. I
would like to be able to change $45,000,000.00 to say 45.0
without re-typing the information. Is there a way to do
this

Thanks in advanc

Penn
 
P

Peter Atherton

Penny

I presume that the data is imported or copied otherwise
you would just type it in direct. The following sub will
divide and format the data that is selected.

To make it work Press Alt + F11 to open the VB Editor.
Choose Insert, Module and Paste this macro.

Sub Test()
Dim c As Variant
'Just in case you select non numerical values
On Error Resume Next
For Each c In Selection
c.Value = c / 1000000
c.NumberFormat = "$0.0"
Next c
End Sub

Close the VB Editor (Alt + Q) and select the column to
change. Choose Tool, Macro Select the macro and press run.

If you think of any other possible problems contact me adn
I'll include more error checking.

Regards
Peter
 
Top