• Status: Solved
• Priority: Medium
• Security: Public
• Views: 259

# 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.
0
zimmer9
• 3
• 3
• 2
• +1
2 Solutions

Author Commented:
0

Commented:
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
0

Commented:
Here's a UDF that would do it.

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

EngineerCommented:
If there are always zeros after the decimal then you can use the format

00000000000"00"
0

Author Commented:
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?
0

Commented:
no, you would enter a cell and use =removedecimal(a1) as any other formula

T
0

EngineerCommented:
What happens when you use the custom format?
0

Author Commented:
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.
0

Commented:
use custom format 0000000000000

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.
0

## Featured Post

• 3
• 3
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.