function or format - remove decimal but retain value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Geraldine - 29 Mar 2008 00:45 GMT

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.

Reply to this Message

Gary''s Student - 29 Mar 2008 00:57 GMT

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.

Signature

Gary''s Student - gsnu200776

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

Reply to this Message

Rick Rothstein (MVP - VB) - 29 Mar 2008 01:16 GMT

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

> Select the cells you want to change and run this macro:
>
[quoted text clipped - 17 lines]
>>
>> Thanks for any suggestions.

Reply to this Message

Ron Rosenfeld - 29 Mar 2008 01:07 GMT

>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

Reply to this Message