Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

How would you convert the values in a field to remove a decimal point from an Excel 2003 file with Custom numbers?

Using Excel 2003 how woud you remove the decimal point from a Custom field as per the attached file? I need to retain the lead zeros.
Avatar of zimmer9
zimmer9
Flag of United States of America image

ASKER

Do you want to see the decimals (as in x100), so 50 would show as 0000005000) or not?

If you don't, use custom format 0000000000000
If you do, use the same custom format but multiply your column by 100.

Thomas
ASKER CERTIFIED SOLUTION
Avatar of Elton Pascua
Elton Pascua
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Saqib Husain
If there are always zeros after the decimal then you can use the format

00000000000"00"
Avatar of zimmer9

ASKER

I hit Alt-F11 and inserted the following into a module:

Function RemoveDecimal(myRange As Range)
    Dim numVal As String
    numVal = Replace(myRange.Text, ".", "")
    RemoveDecimal = numVal
End Function

How would I execute thsi function?

Would I got back to the worksheet and then click on Alt-F8, then type in the Macro name   RemoveDecimal?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What happens when you use the custom format?
Avatar of zimmer9

ASKER

I need the cents for example 00000001937.50    needs to become 0000000193750    and not

0000000193800 (rounding up is not what I need).

I need to in effect multiply all values by 100 to shift the decimal point to the right 2 positions.
use custom format 0000000000000
multiply your column by 100

put 100 in a cell, copy it, select your number columns, paste special, values, multiply.

You're done.

No real need for macro unless you'll do that a lot.