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.

7,418 comments

  1. 더킹카지노

    What’s Going down i’m new to this, I stumbled upon this I’ve discovered It absolutely helpful and
    it has helped me out loads. I hope to give a contribution & assist different users like its helped me.
    Good job.

  2. 퍼스트카지노

    I just couldn’t leave your website prior to suggesting that I really loved the usual information an individual supply in your
    visitors? Is gonna be back regularly to check out new posts

  3. バカラ

    You actually make it seem so easy with your presentation but I find this
    topic to be really something that I think I would never
    understand. It seems too complex and extremely broad
    for me. I’m looking forward for your next post, I’ll try to get the hang of
    it!

  4. 카지노사이트

    An interesting discussion is definitely worth comment.
    There’s no doubt that that you need to publish more about this subject, it might
    not be a taboo matter but generally folks don’t speak about
    these subjects. To the next! Kind regards!!

  5. homepage

    Woah! I’m really loving the template/theme of this website.
    It’s simple, yet effective. A lot of times it’s hard too get that “perfect balance”
    between user frienddliness and visual appearance.
    I must say you have done a ver good jjob with this.
    Also, the blog loads extremely quick for me on Chrome.

    Excellent Blog!
    homepage

  6. perfect gift

    You really make it seem so easy with your presentation but I find this matter too
    be really something that I think I would never understand.
    It seems too complicated and very broad for me. I’m looking forward
    for your next post, I will try to get the hang of it!

  7. 우리 카지노

    Hello just wanted to give you a quick heads up. The text in your content seem to be
    running off the screen in Internet explorer.
    I’m not sure if this is a formatting issue or something to do with internet browser compatibility but I figured I’d post to let you know.
    The layout look great though! Hope you get the problem solved soon. Cheers

  8. Malaysia Online Casino

    Does your website have a contact page? I’m having problems locating it but,
    I’d like to send you an email. I’ve got some ideas for
    your blog you might be interested in hearing.

    Either way, great site and I look forward to seeing it develop over time.

  9. fun88 mobile

    I wanted to thank you for this great read!! I certainly enjoyed every little bit of it.
    I have got you book-marked to look at new stuff you post…

  10. 퍼스트카지노

    Spot on with this write-up, I absolutely feel this website needs
    much more attention. I’ll probably be returning to read
    through more, thanks for the info!

  11. garoto de programa balneário

    It’s an awesome piece of writing in favor of all
    the internet users; they will take benefit from it I am sure.

  12. womans sex toys

    If some one desires to be updated with hottest technologies after that he must be pay a
    visit this site and be up to date daily.

  13. 먹튀사이트

    Itís nearly impossible to find well-informed people on this topic, however, you sound like you know what youíre talking about! Thanks

  14. Kathlene Woodley

    Thanks for sharing your thoughts about 더킹카지노.
    Regards

  15. 더킹카지노

    There’s certainly a lot to know about this issue. I like all of the points you’ve
    made.

  16. lildurk.com

    Actually when someone doesn’t be aware of then its up to other
    people that they will assist, so here it occurs.

  17. 샌즈카지노

    Hello are using WordPress for your blog platform?
    I’m new to the blog world but I’m trying to get started and create my own. Do you need any html coding knowledge to make your own blog?
    Any help would be greatly appreciated!

  18. 세종오피

    This is a very good tip especially to those new to the blogosphere.
    Simple but very accurate information… Appreciate your sharing
    this one. A must read article!

  19. 대구안마변경주소

    Hi! I know this is kinda off topic however I’d figured I’d ask.
    Would you be interested in trading links or maybe guest writing a blog article or vice-versa?
    My blog goes over a lot of the same subjects as yours and I think we could greatly benefit from each other.
    If you happen to be interested feel free to shoot me an email.

    I look forward to hearing from you! Terrific blog by the way!

  20. 메리트카지노

    Hi there, constantly i used to check weblog posts here in the
    early hours in the morning, since i love to learn more and more.

  21. 오피스타

    Everyone loves what you guys tend to be up too.
    This type of clever work and coverage! Keep up the amazing works guys
    I’ve incorporated you guys to our blogroll.

  22. 라이브 바카라

    Thanks in favor of sharing such a pleasant idea, article is nice, thats why i have read it fully

  23. 먹튀커뮤니티

    Spot on with this write-up, I seriously believe this web site needs far
    more attention. I’ll probably be returning to see more, thanks for the information!

  24. 온라인룰렛

    Great blog! Do you have any helpful hints for aspiring writers?
    I’m planning to start my own blog soon but I’m a little lost on everything.
    Would you recommend starting with a free platform like WordPress or go for a paid option?
    There are so many choices out there that I’m completely overwhelmed ..
    Any recommendations? Many thanks!

  25. agree with this

    If some οne wants expert view conceгning bⅼoggіng and site-buiⅼding aafter
    that i propos him/her tto pay a quick visit this websitе, Keep up the nkce work.

  26. can i take cbd oil and lorazepam

    Hello, Neat post. There is a problem with your site in web explorer, could check this?
    IE still is the market leader and a large component to other folks will pass
    over your magnificent writing because of this problem.

Leave a Reply

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