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

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
Asked:
zimmer9
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
zimmer9Author Commented:
0
 
nutschCommented:
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
 
Elton PascuaCommented:
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

Open in new window

xxx.xls
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Saqib Husain, SyedEngineerCommented:
If there are always zeros after the decimal then you can use the format

00000000000"00"
0
 
zimmer9Author 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
 
nutschCommented:
no, you would enter a cell and use =removedecimal(a1) as any other formula

T
0
 
Saqib Husain, SyedEngineerCommented:
What happens when you use the custom format?
0
 
zimmer9Author 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
 
nutschCommented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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