Padding a text column with leading zeros

[Guest]

I have a text file with a column containing numeric codes. I need to 'pad'
the code values with leading zeros--so that all of the codes are of the same
length. I imported the file into a new workbook, specifying the code column
to be of type text.

I've tried every method and searched the web and newsgroups every way, but
can't figure out how to pad the column. The many references for Excel 2003
don't seem to apply to Excel 2007. Can someone help?

 

[Niek Otten]

If the fixed length has to be 10:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a text file with a column containing numeric codes. I need to 'pad'
| the code values with leading zeros--so that all of the codes are of the same
| length. I imported the file into a new workbook, specifying the code column
| to be of type text.
|
| I've tried every method and searched the web and newsgroups every way, but
| can't figure out how to pad the column. The many references for Excel 2003
| don't seem to apply to Excel 2007. Can someone help?

 

[Bernard Liengme]

Are they all composed of digits only?
Then use Custom format such as "0000000000"
best wishes

 

[Guest]

Yes, they are all composed of the numbers 0 through 9.

Prior to my posting my original message, I selected the entire column and
then applied a custom format of "0000000000" (as numerous online references
was the way to do it--at least, for Excel 2003); however, nothing happened.

 

[Bernard Liengme]

Seems the cells are full of text values notwithstanding you have only
digits.
Try in a separate cell entering 1234; then give it the custom format just to
be sure we are on the same wavelength.
Feel free to send me sample file (my private email not the newsgroup)
best wishes

 

[Guest]

Hmm...interesting. The custom formatting works on a separate cell!

There are about 10,000 rows in this file. Figuring that (perhaps) one or
more of the rows has something other than numeric digits in the target
column, I selected just a few consecutive cells within the target column
(they *definitely* contain just numeric digits) and applied the custom
formatting to them. It still didn't work! So what's the problem with this
column and with these cells?

 

[Bernard Liengme]

Maybe there are hidden spaces after the last character.
Let's say you have one of these in A1
What do these formulas return:
=2*A1
=1*A1 and then use the custom format if you do not get an error result
=ISNUMBER(A1)

I'm sure we can solve this with some patience!
best wishes

 

[Phillip McClore]

I am using msOffice Excel 2007, and this worked beautifully! I actually needed 12 digits with a leading zero. Until I found this asking around the office, and a little cerfing what a great find!

Thanks,
Juan