Microsoft Excel - I am trying to get my leading zeros to display in excel for multiple length numbers

Asked By Gregg Frazier on 29-Jan-10 06:42 PM
HELP Please
I have a spreadsheet with numbers in the same column (A) that are either 8 digits or 12 digits in length.
Ex. A barcode scanned into excel with 8 digits 00123456 displays like 123456 while in the same column i have 12 digit barcodes that read 000000123456display but in excel display as 123456 also. There are significant differences in the information in the following cells, but i need to have the original zeros displayed so i can then convert the correct info into another program.
MAhalo Nui Loa for your kokua
(Hawaiian for Thank you very much for your help)

Do these. - [)ia6l0 iii replied to Gregg Frazier on 29-Jan-10 09:16 PM

If you do not have any data in the excel file, and you are just beginning, you can simply format the column as Text . Right-click on the column header, Choose Format Cells - Select Text Press Okay.

If it is only a few cells, then prefix the values with an apostrophe. That will retain the leading values in the cells.

And there are few other methods that can be applied to your case as suggested by this Microsoft Knowledge base article  titled "Using a custom number format to display leading zeros"

Hope that helps.
mv ark replied to Gregg Frazier on 29-Jan-10 10:03 PM
You can format the cells to have a zero prefix by applying the desired number format to a range of cells. To apply this, select the entire column, right click & choose the Format Cells option. In the Number tab of the Format Cells dialog box, select Special & specify the format as 00000000 (that's 8 zeroes). Once you set this, numbers of up to 8 digits would be prefixed with zeroes. This may however work if the numbers are of a fixed length (8 or 12 but not both)

If you don't mind treating the numbers as text, type this formula into cell B1 (& drag it whatever column range you require it for) -

This would work for both 8 or 12 digit numbers. You can consider hiding the column A, when you done with that sheet.
Jonathan VH replied to Gregg Frazier on 30-Jan-10 07:58 AM

You should not use a custom number format if you have a mix of eight and twelve digit numbers and you are "converting" the data into another program.  These data are not numbers (even though they are composed of numeric digits), they are strings (text), and should be stored as such in Excel.  Excel automatically converts values composed of numeric digits into numers unless you tell it otherwise.  The best way to do this, as mentioned above, is to preformat the cells as Text.  That's how you direct Excel to "leave the zeros alone."

If you cannot re-import the values into a Text range, you can insert a (temporary) column next to your column of numbers and use a formula like this copied down the rows:


Then Copy that range of formulas and Paste Special, Formats over your original column (D, in this example).  Then delete the temporary column with the formulas.

If you have some other algorithm to distinguish the eight-digit values from the twelve-digit values, i.e. a value from another column, use that instead of the predicate I used above, as my algorithm will convert what was 000012345678 to 12345678.

To convert an existing range from numbers to text, you can select the range, format the cells as Text, and then use Alt+d, e, Enter, Enter, Enter (Text to columns), but the leading zeros were lost when Excel converted the values into numbers and would need to be replaced.

Gregg Frazier replied to [)ia6l0 iii on 30-Jan-10 02:53 PM
formatting the column FIRST as text. ah... so simple and it works!!!!!
THANK YOU! This is a problem we have been dealing with for so long at our company and it will make my life so much easier.
Mahalo Nui Loa for your Kokua