Use Text Format to Preserve Leading Zeros in Excel [Quick Tip]

Posted on February 15th, 2012 in Excel Howtos - 26 comments

Here is a quick tip to add awesome to your Wednesday.

If you want to enter numbers like 00023 or 023.340 or 23.34500 in your Excel sheet, you would notice that Excel magically removes leading zeros and trailing zeros (after decimal point) as the number 23 is same as 00023. But sometime, we want 00023, not 23. Then what?!?

Very simple, we use TEXT format instead of number format. Just select the cells where you are going to enter these numbers, and from Home ribbon > Number area, select “Text” as cell type. This tells Excel to treat any value you enter as Text, not as number. So when you type 00023, it will appear as 00023.

See this short demo to understand how to get this work.

How to preserve leading zeros in a cell - Excel Tutorial

Bonus Tip – Use fixed number of zeros

Applying Fixed Digit Formatting to Numbers in ExcelFor example, if you want the number to show up in 5 digits (with leading 0s if needed), you can use the cell format code 00000.

To apply this format:

  • Just select the cells and press CTRL+1
  • From Number tab choose “Custom”
  • Enter the format code as 00000
  • Done!

Aside, you can see how this formatting works.

That is all for now. Have a great evening then :)

More on Cell Formatting

Excel allows you to format cells in myriad ways, some of which may baffle you. But Chandoo.org got your back! We have written several articles to help you master the cell formatting. Read on,

PS: We had a minor hiccup with our newsletter. Many of you did not get it for last 7 days. It is fixed now. So you might get one big email from Chandoo.org with all the missed posts.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

26 Responses to “Use Text Format to Preserve Leading Zeros in Excel [Quick Tip]”

  1. Ashish Kulkarni says:

    You could always use a leading ‘ also, of course.

  2. Jon Peltier says:

    It’s probably best to use the custom number format than formatting as text out using the leading apostrophe. The only exception that comes to mind is if the number is a credit card number. Excel lewis only the first 15 digits, and turns the rest to zeros.

  3. Fred says:

    It doesn’t really work, I’m sorry.

    After setting the “00000″ in custom. The number does change to “00023″. However, if you go back to cell format, it will show up under “Special” -> “Zip Code”. i’m using excel 2007.

    Also, I have customer ID that starts with zero from another system. When that ID (along with a table of data) is pulled into Excel it will show as 000023 with a cell format “General”. if you F2 that cell the zeros would be gone so I don’t want to mess with it.

    The problem is that you can’t do vlookup between 2 cells one with a zip code format and the other one as General. I have seen that in the past and it won’t work.

    I ended up getting a different solution, not smart/efficient but works for me. I enter “000″&23, copy and paste as value on the same cell. This way, the ID 00023 generated by a different system dl to Excel is compatible to other data. To make my life easier I have generated, on the side, a list of customers whose ID start with 0. When I need them, I’ll just pull them to the worksheet where I need them.

  4. Jon Peltier says:

    Fred -

    The problem is not a lookup in different formats, it’s that some of your values are numerical and some text.

    Your values that visibly change when you use the 00000 format are numbers. The values that come in from another system with leading zeros, come in as text which preserves the zeros as text. The General format shows the text complete with leading zeros and numbers without any leading zeros and without trailing zeros after the decimal point. F2 makes Excel re-evaluate the numbers, and converts them from text with leading zeros to numbers without.

    If it were my data, I’d convert everything to numbers and apply appropriate number formats. Then I wouldn’t have to keep a separate list of special companies, and change my data whenever one of them were involved.

  5. RichardS says:

    It seems like a really bad idea to suggest using text to preserve leading zeros, particularly in light of all of the concern these days abut spreadsheet integrity and managing risks in spreadsheets.

    • Chandoo says:

      Very good point. When you are entering numbers, I see no reason why you would want leading zeros. But when you are entering text data (such as customer numbers, invoice numbers, product codes) that looks like numbers, then using TEXT format is actually a better choice since you are not going to sum-up or do other such arithmetic with them.

      • Jon Peltier says:

        If a field contains entries that may be numbers or text, then treat the field as text. If the field only contains numbers, I still think you should retain their numerical values and adjust their appearance using number formats.

        • Ken says:

          Beware if doing arithmetic operations to a “Text Number” – =/+ an individual cell will return something meaningfull, SUM() will ignore a non-numeric style and return 0. Prefacing with a ‘ will also generate the same behaviour.

      • Murugaraj says:

        Chandoo, sometimes these numbers are expected in a fixed format.
        In where I work, the employee id is always in 7 digits. So in columns that indicate employee id, we always format with Custom 7 digits. this really helps easy reading.
        I always use the Custom format rather than the text or using ‘ before the numbers because the latter two cases will not help using any formulas.

        • Shawn Wheatley says:

          Murugaraj, even if the employee ID is stored in a company database as an integer, you’re never going to be treating the ID as a number (such as performing arithmetic on it.) A system I work with has a 10 digit ID that is all numbers, but starts with zeroes in some cases. We always treat these IDs as strings.

          One problem I have is that even though they’re strings, exporting CSV from the database results in columns that are treated as numbers and lose the starting zeroes. In this case, I usually add another column with the formula =REPT(0,8-LEN(A1))&A1. This formula will create a column with a string padded with the correct number of zeroes (enough for 8 digits in this example.)

  6. Excel Chimp says:

    I frequently need to change a list mixed with 3, 4, and 5 digits to uniformly 5 digits by adding leading zeros.
    I was excited about the custom format solution, but then I realized the underlying cell value is still 3 or 4 digits, realizing that custom formatting is just that, merely a format applied to a value. If I copy and paste to another location, the 3 or 4 digit values remain.

    The other way to write a formula such as =”0″&A1 but I like your method better. Any workaround to make it work in my case?

  7. Jon Peltier says:

    You could use

    =TEXT(A1,”00000″)

    which shows a 5-digit number in the cell but treats it as text. Copy and paste deals with the 5-digit text, not the underlying numerical value.

  8. Santosh Arisetty says:

    I am a newbie to excel and never got an opportunity to explore much on it. As I started working on it, the results were startling. I am really impressed by the way the site is organized and I am book marking this site as a one stop to my excel problems.

  9. Nelson says:

    How to format 28,654,124 to show as 28,000,000 using custom formatting (without using round() formula)

  10. tmk says:

    How to VLOOKUP serial IDs with leading zeros & without zeros

  11. Rebecca says:

    I have an issue when doing sumif functions for items that have leading zero’s. The item numbers in our production facility may or may not have leading zero’s and can match if you drop the leading zero. For example; item number 12399 is not the same item as 012399 if our system. If I am doing a sumif on the item number, even if both items are formatted as text, excel will add the column that I am summing together for both items. If I did a sumif on the info below to get total yearly sales excel will return 10 for both item numbers. this is causing some real issues for me. Does anyone have a solution?
    Item Sales Qty Month
    ’12399 5 Jan
    ’012399 5 Feb

  12. Julie says:

    I have tried all your suggestions above…. still can’t get leading zeros back into zip code field that were dropped when I imported a csv file.  I saw in the custom format where I could add “0″ back in but only to five digits — my zip code field has all 9 digits (zip plus4) all together
    Example1:  9digit Zip code field for Puerto Rico After import: 6105458
    should be: 006055132
     
    Example 2:  9 digit Zip code field for MA After import: 19601714
    should be 019601714
     
    I tried all of the suggestions above – can’t get anything to work — any other suggestions – or ideas what I am doing wrong? 

  13. Martin B says:

    If you have your ZIP-code in cell A1, you could try the following formula in cell B1 =IF(LEN(A1)<9;REPT("0";9-LEN(A1))&A1;A1)

    I know this is a poor work-around :-(

  14. Ola says:

    God bless you real good, you just saved someone’s ass in ma office !

  15. Shaan @ PNR Status says:

    I was trying to add zero in my excel but it’s not happening. I was quite frustrated and i start searching the way to do it and luckily i got this page. I have did according to your described way and it’s working. Thanks for sharing such a valuable resource.

Leave a Reply