Keep Leading Zeros

October 3rd, 2004 | Categories: Formatting | Tags:

If you work with product numbers, zip codes, or any number that starts with a leading zero you will probably want Excel to keep the leading zero visible.

By default when you type the product number 012345 into a number formatted cell Excel will drop the leading zero and show 12345. To keep the leading zero you can use a custom number format.

leadingzero1

1. Right click the cell to custom format and select Format Cells
2. In the Category box select Custom
3. In the Type box enter a zero for each digit in your number. In this case I entered 6 zeros because my product number was 6 digits long.
4. Hit OK

leadingzero2

Also note: If you are using a Zip Code, Zip Code + 4, Phone Number, or Social Security Number that begins with a zero, Excel has built in number formats for these. You can access them by selecting Special instead of Custom in the Category box. Also make sure your Locale is set to English.


Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  1. Patrick
    December 16th, 2004 at 14:50
    Reply | Quote | #1

    Thanks for the tip, I have dealt with 24hr time for quite sometime now without the leading zero. There is a ” time format for 24hr time but ” semicolons” are needed.
    Thanks again

  2. Mark
    December 22nd, 2004 at 20:07
    Reply | Quote | #2

    No problem. I used to work with part numbers in spreadsheets quite often, keeping leading zeros was a must.

  3. Mike
    November 22nd, 2008 at 18:09
    Reply | Quote | #3

    So how do I merge tow cols both text one has leading zeros?
    =A1&” “&D1 just doesn’t work it truncates the leading zeros even if the source and target are text fields

  4. Ivar
    June 15th, 2009 at 16:04
    Reply | Quote | #4

    You merge two columns by way of the concatenate function

    =CONCATENATE(A1,B2)

  5. Dan
    January 22nd, 2010 at 16:43
    Reply | Quote | #5

    I am trying to concatenate address fields including the zip. I try using the zip format and the custom format, but the zero still gets dropped when I concatenate. Is there any way of keeping the leading zero when the fields are merged?

    • Rudy
      February 11th, 2010 at 22:26
      Reply | Quote | #6

      =TEXT(A1,”00000″)&” “&B1 will preserve the leading zero.

  6. Hendrix67
    August 13th, 2010 at 13:59
    Reply | Quote | #7

    Thanks! this really saved me many hours of tedious work.

  7. November 29th, 2010 at 21:42
    Reply | Quote | #8

    Topping the report on changes this current year is some sort of sharp lowering the number of Medicare convenience plans….Government experiments have concluded the ‘advantage’ in most of the Medicare edge plans goes this is not to the policy holders, but for the bottom brand of the insurance companies selling that policies, and there’s a move because of the government to be able to phase these people out….the other one significant change this year is alot more prescription medicine coverage for a lot of, but only a few, Medicare beneficiaries signed up for the non-reflex drug prepare

  8. Susan
    April 14th, 2011 at 20:33
    Reply | Quote | #9

    How about showing variable number of leading zeros in variable number of digits? For example, I want to be able to enter and show “001234″ or “0000123456789″?

  9. David
    April 27th, 2011 at 01:39

    God this saved me so much time and frustration. A program I work with exports in csv format only, which Excel automatically strips of leading zeros. This is a quick way to rectify.

  10. Diane
    May 11th, 2011 at 18:58

    I subtotalled a spreadsheet adding the total to cells I had formatted as Text. I now am trying to use Find/Replace to get rid of the “Total” from the text but it keeps removing all the zeros at the same time….help

  11. khalid
    July 29th, 2011 at 17:27

    by this method we cannot copy zero.

  12. Maleny
    September 19th, 2011 at 18:56

    hello, i ran into this trying to keep leading zeros, but i need to keep them in date format. so, instead or 1-May, i need 01-May, help! i have been tryin to do this for hours and i cant get it!

    • Bob
      November 9th, 2012 at 20:23

      Again, it’s a Custom format. The date formats are all based on d (day), m (month) and y (Year). So for your example: dd-mmm – where one d would get rid of the leading zero and two keeps it. Similarly, two x mm gives a short month format, 3 x mmm a longer format, and likewise for yy (just the last two digits of the year) and yyyy (full year with century). What comes between can be a space, a -, a / etc. Try playing around to get the hang.