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.

5,501 comments

  1. canli bahis siteleri listesi

    Thanks for sharing your thoughts. I truly appreciate your efforts and I am waiting for
    your next write ups thank you once again.

  2. glove box

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

  3. Ona

    Great work! That is the kind of info that are supposed to be shared across the web.
    Disgrace on the search engines for now not positioning this post higher!
    Come on over and visit my site . Thanks =)

    Here is my blog: خرید رپورتاژ (Ona)

  4. us casino player sites

    Odds of 3-1 indicate that if you bet $one hundred, you will win $400, the original amount of your bet plus the profit.

  5. baseball prediction

    Incorporating reside and season player stats and our Opta ball tracker visualisation, this is the ultimate tool for premium player betting and fantasy experiences.

  6. sugar daddy sites

    Hello, i believe that i noticed you visited my weblog so
    i got here to ?return the desire?.I am trying to in finding things to improve my website!I suppose its adequate to make use of some
    of your ideas!!

    Have a look at my webpage – sugar daddy sites

  7. League information

    Your “wait” is on the other hand extended it requires you to stroll from the sportsbook region of the casino to the
    cage.

  8. baseball picks

    Hе hаѕ еnѕurеd thаt thе рiсkѕ that wоuld
    bе gеnеrаtеd bу thе sроrtѕ tоtо chаmр are саrеfullу саlсulаtеd еасh time bеfоrе it iѕ ѕеnt
    to you tо еnѕurе уоur сhаnсеѕ
    оf winning.

  9. lab glove box

    I am really grateful to the owner of this website who has shared this impressive
    paragraph at at this time.

  10. Norma

    Excellent article. I definitely appreciate this website.
    Keep writing!

  11. MKsOrb

    Add in bold eye-catching textual content that helps promote your
    service.

  12. Penney

    Hi there mates, its enormous paragraph regarding cultureand completely defined, keep it up all
    the time.

  13. Handball

    The NFL odds compilers will give the supposedly stronger group a points handicap in order to
    even things up.

  14. Nichwell glove box

    You are so cool! I don’t think I’ve read through a single
    thing like that before. So good to discover someone
    with a few original thoughts on this subject
    matter. Really.. thanks for starting this up. This site is something
    that’s needed on the web, someone with a bit of originality!

  15. waxing hair removal

    They had been typically featured on Television when analysts previewed games,
    as Nevada was the only state with legal sports wagering.

  16. 2016년 1분기 애니 순위

    You could definitely see your skills within the article you write.
    The world hopes for even more passionate writers such as you who aren’t afraid to mention how they believe.

    Always go after your heart.

  17. 해외 토렌트 사이트 추천

    I’m not sure exactly why but this web site is loading
    incredibly slow for me. Is anyone else having this issue or is it a problem on my end?
    I’ll check back later on and see if the problem still exists.

  18. online betting online

    This is exactly where items may get a tiny confusing for
    these unfamiliar with American odds.

  19. pubic hair removal

    This is majorly since of the simplicity and ease of use of the web-site.

  20. 대구출장마사지

    Pretty! This was an incredibly wonderful post. Many thanks for supplying this info.

Leave a Reply

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