Padding a text column with leading zeros
 

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?

Padding a text column with leading zeros
 

If the fixed length has to be 10:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Chris" wrote in message ...
|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?

Padding a text column with leading zeros
 

Are they all composed of digits only?
Then use Custom format such as "0000000000"
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" wrote in message
...
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?

Padding a text column with leading zeros
 

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" wrote:

Are they all composed of digits only?
Then use Custom format such as "0000000000"
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" wrote in message
...
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?

Padding a text column with leading zeros
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" wrote in message
...
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" wrote:

Are they all composed of digits only?
Then use Custom format such as "0000000000"
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" wrote in message
...
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?

Padding a text column with leading zeros
 

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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" wrote in message
...
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" wrote:

Are they all composed of digits only?
Then use Custom format such as "0000000000"
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" wrote in message
...
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?

Padding a text column with leading zeros
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" wrote in message
...
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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" wrote in message
...
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" wrote:

Are they all composed of digits only?
Then use Custom format such as "0000000000"
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" wrote in message
...
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?