function or format - remove decimal but retain value

[Geraldine]

I need to take a column of numbers 1235.36 and change to 123536 with a number
of leading zeros. End result 0000123536. Is there a format or function to
accomplish this task?

Thanks for any suggestions.

 

[Gary''s Student]

Select the cells you want to change and run this macro:

Sub fixer()
For Each r In Selection
With r
v = "0000" & Replace(.Text, ".", "")
.Clear
.NumberFormat = "@"
.Value = v
End With
Next
End Sub

It removes the decimal point and puts the 4 zeros in front.

 

[Ron Rosenfeld]

I need to take a column of numbers 1235.36 and change to 123536 with a number
of leading zeros. End result 0000123536. Is there a format or function to
accomplish this task?

Thanks for any suggestions.

=TEXT(INT(A1*100),"0000000000")

If you require the values be numeric, then:

=A1*100 and custom format the cell as "0000000000"


--ron

 

[Rick Rothstein \(MVP - VB\)]

I'm thinking the number may vary in "length" and the number of leading
zeroes may vary with it. It looked to me like the OP might want to keep the
field length fixed at 10 digits (using leading zeroes to pad it out to that
length).

Sub FixIt()
Dim R As Range
For Each R In Selection
R.Value = Format(Replace(R.Value, ".", ""), "'0000000000")
Next
End Sub

Instead of using NumberFormat, I simply preceded the cell value with a
leading apostrophe. Is there any downside to enforcing the text format on
the numerical result with the apostrophe rather than using a NumberFormat?

By the way, my code may not be the answer the OP is looking for either... it
depends on if the number of decimal places can vary and how that would
affect the alignment of the number once the decimal point is removed.

Rick