zimmer9
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If there are always zeros after the decimal then you can use the format
00000000000"00"
00000000000"00"
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What happens when you use the custom format?
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.
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.
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.
ASKER
xxx.xlsx