| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| TEXT TO COLUMNS WITH LEADING ZEROS | Excel Discussion (Misc queries) | |||
| Padding with leading zeros | Excel Discussion (Misc queries) | |||
| Remove leading zeros from column | Excel Discussion (Misc queries) | |||
| save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) | |||
| Adding Leading Zeros to Text | Excel Discussion (Misc queries) | |||
Linear Mode
