How to Keep Leading Zero in Excel

By default, when entering numbers into Excel, leading zeros will be removed. This can be an issue when entering phone numbers and IDs. In this article, I will show you several ways to solve this problem and keep the leading zeros.

1. One-time Solution : Keep the Leading Zero as you Type

If you wanted to ensure that the leading zero is kept when typing, enter a single quote before you type the number.

excel leading zero

This instructs Excel to store the value as text and not as a number.

When you press “Enter” to confirm, excel will show a green triangle in the top left corner of the cell. Excel is checking that you intended to do that or if you want to convert it to a number.

Click the diamond icon to display a list of actions. Select “Ignore Error’ to proceed and store the number as text.
excel convert to number

The green triangle should then disappear. This solution will only work every time you type single quote as shown above. To make excel allow leading zeros all the time, follow the next solutions.

2. Apply Formatting

If you are planning to have a lot of leading zeros in your document, you need to consider this solution.

Select the range of cells you want to format as text. Next, click the “Home” tab, select the list arrow in the Number group, and choose “Text.”

excel change format to text

The values you enter into this formatted range will now automatically be stored as text, and leading zeros preserved.

3. Keep Leading Zeros to Make Fixed Width

The previous two options are great and sufficient for most needs. But what if you needed it as a number because you are to perform some calculations on it?

For example, maybe you have an ID number for invoices you have in a list. These ID numbers are exactly five characters in length for consistency such as 00055 and 03116.

To perform basic calculations such as adding or subtracting one to increment the invoice number automatically, it should be stored as a number to perform such a calculation.

Select the range of cells you want to format. Right-click the selected range and click “Format Cells.”

excel format cells

From the “Number” tab, select “Custom” in the Category list and enter 00000 into the Type field.

excel format cells custom

Entering the five zeros forces a fixed-length number format. If just three numbers are entered into the cell, excel will add two extra zeros automatically to the beginning of the number.

excel leading zeros

You can play around with custom number formatting to get the exact format you require.

11,867 comments

  1. 메리트 카지노

    Very good info. Lucky me I recently found your site by chance (stumbleupon).
    I have bookmarked it for later!

  2. เกมบาคาร่า

    Hi there, I want to subscribe for this webpage to
    get most recent updates, so where can i do it please help out.

  3. Twin

    I was recommended this web site by way of my cousin. I am no
    longer sure whether or not this put up is written by way of him as nobody else know such distinctive approximately my problem.
    You are amazing! Thank you!

  4. betway

    What’s up to all, the contents existing at this web page are really amazing for people experience, well, keep
    up the nice work fellows.

  5. Porn

    Hi, I check your blog regularly. Your writing style is awesome, keep it up!

  6. Blackjack

    Thank you for sharing your thoughts. I truly appreciate your efforts
    and I am waiting for your next post thanks once
    again.

  7. 카지노 사이트

    I am in fact pleased to glance at this webpage posts
    which carries plenty of valuable facts, thanks for providing these kinds of
    information.

  8. 벳365 한글

    This page certainly has all of the info I wanted about this subject and didn’t know who
    to ask.

  9. Titanium inife

    Nice post. I was checking constantly this blog and I’m impressed!
    Extremely helpful info particularly the last part 🙂 I care for such information a lot.
    I was seeking this certain info for a very long time.
    Thank you and good luck.

  10. секс игрушки

    I used to be suggested this web site via my cousin. I’m not positive whether or not this put up is written by means of him
    as no one else understand such certain approximately my difficulty.
    You’re wonderful! Thank you!

    Here is my website :: секс игрушки

  11. 더킹카지노

    Hey there would you mind letting me know which webhost you’re
    using? I’ve loaded your blog in 3 different internet browsers and
    I must say this blog loads a lot quicker then most. Can you suggest a good internet hosting provider at a reasonable price?

    Many thanks, I appreciate it!

  12. 극단적 인 애널 재미 2034

    This is very interesting, You are a very skilled blogger. I have joined
    your rss feed and look forward to seeking more of your fantastic post.
    Also, I’ve shared your site in my social networks!

  13. Payday loans

    Very nice post. I just stumbled upon your weblog and wanted to say that I have truly enjoyed surfing around your blog
    posts. In any case I’ll be subscribing to your feed and I hope you write again very soon!

  14. Titanium Bike

    I will immediately grab your rss as I can’t in finding your e-mail subscription link or e-newsletter service.
    Do you’ve any? Please let me know in order that I may just subscribe.
    Thanks.

  15. XXX

    Excellent article. I’m experiencing some of these issues as well..

  16. indian players

    This post is genuinely a fastidious one it assists new the web people, who are wishing for blogging.

  17. 퍼스트 카지노 주소

    Wow that was unusual. I just wrote an extremely long comment but after I clicked submit my comment didn’t appear.
    Grrrr… well I’m not writing all that over again.
    Anyways, just wanted to say superb blog!

  18. 메리트카지노

    Hi there! I just wanted to ask if you ever have any issues with hackers?
    My last blog (wordpress) was hacked and I ended up losing several weeks of hard work due to no backup.
    Do you have any solutions to stop hackers?

  19. 라이브 카지노

    Hmm is anyone else encountering problems with the pictures on this blog loading?
    I’m trying to figure out if its a problem on my end
    or if it’s the blog. Any feedback would be greatly appreciated.

  20. canvas photo prints

    What’s Happening i am new to this, I stumbled upon this
    I have discovered It positively useful and it has helped me out loads.
    I hope to contribute & assist other users like its aided me.
    Good job.

  21. 중고 이 이 이 1

    I think the admin of this web page is in fact working hard in support of his web
    site, because here every data is quality based information.

  22. 퍼스트카지노

    Asking questions are genuinely nice thing if you are not understanding something completely, except
    this paragraph offers nice understanding even.

Leave a Reply

Your email address will not be published. Required fields are marked *