Solved

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

Posted on 2012-08-23
257 Views
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
Question by:zimmer9

Author Comment

0

LVL 39

Expert Comment

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

LVL 8

Accepted Solution

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

LVL 43

Expert Comment

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

00000000000"00"
0

Author Comment

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

LVL 39

Assisted Solution

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

T
0

LVL 43

Expert Comment

What happens when you use the custom format?
0

Author Comment

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

LVL 39

Expert Comment

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…