Padding a Number

[Chris]

I have a text column of numbers that range from 6 to 9
characters in length. I need to left pad with zeros so
that all of the numbers are 9 characters in length. Can
you tell me the function I should use and possibly provide
an example?

TIA
Chris

 

[Anon]

I have a text column of numbers that range from 6 to 9
characters in length. I need to left pad with zeros so
that all of the numbers are 9 characters in length. Can
you tell me the function I should use and possibly provide
an example?

TIA
Chris

One way:
=RIGHT("000"&A1,9)

 

[Don Guillett]

try format>custom format>000000000 (that's 9 0's)

 

[Chris Brown]

Worked perfect! Thanks!

chris

-----Original Message-----


One way:
=RIGHT("000"&A1,9)


.

 

[Chris Brown]

thanks

-----Original Message-----
Try the following, with number in A1:

=REPT(0,9-LEN(A1))&A1

--
HTH,
Laura Cook
Appleton, WI





.

 

[Chris Brown]

-----Original Message-----
try format>custom format>000000000 (that's 9 0's)

--
Don Guillett
SalesAid Software
Granite Shoals, TX
[email protected]



.

 

[Anon]

Quite right. OP said he had a text column. You suggested he try a custom
format. Applying a custom format to a text column will do nothing. It has to
be converted from text first, which you didn't mention.

 

[Don Guillett]

Sub FixRangeValues()
For Each c In Selection
c.Value = Format(c, "00")
Next
End Sub