I am using Windows XP and Office 2003.

I need a formula to left pad some ID numbers that contain both letters and
numbers and can be from three to seven characters in length so that all IDs
have ten characters. This conversion will occur in another column. For
example:

8912 would become 0000008912
42138A would become 000042138A

I tried: =Text(A1, "0000000") and this works great when the ID is all
numbers, but this fails if letters are involved.

Hi,
Assuming the originals are in Column A and you want the padded version
in column B this should work (you may have to format both columns as
text)

=REPT("0",10-LEN(A1))&A1

HTH,
OJ

That works great! Thanks OJ! - and good night.

"OJ" wrote:

Hi,
Assuming the originals are in Column A and you want the padded version
in column B this should work (you may have to format both columns as
text)

=REPT("0",10-LEN(A1))&A1

HTH,
OJ

(you may have to format both columns as text)

Just to add then:

You don't need to format either column as text. the results will be text
irrespective of formatting.

--
Regards,
Tom Ogilvy


"OJ" wrote in message
oups.com...
Hi,
Assuming the originals are in Column A and you want the padded version
in column B this should work (you may have to format both columns as
text)

=REPT("0",10-LEN(A1))&A1

HTH,
OJ