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.

12,532 comments

  1. オンラインカジノ おすすめ

    My family members all the time say that I am wasting my
    time here at web, but I know I am getting experience every day by reading such good content.

  2. Sex Videos

    With havin so much written content do you ever run into any issues of plagorism or copyright violation? My blog has a lot of unique content I’ve either authored myself or outsourced but it seems a lot of
    it is popping it up all over the web without my authorization. Do you know any techniques to
    help protect against content from being stolen? I’d truly appreciate it.

  3. Payday loans

    Hi would you mind letting me know which webhost you’re using?
    I’ve loaded your blog in 3 completely different web browsers
    and I must say this blog loads a lot quicker then most.

    Can you suggest a good web hosting provider at a fair
    price? Cheers, I appreciate it!

  4. สล็อต

    Thanks for every other informative website. Where else may
    I am getting that kind of information written in such
    an ideal way? I’ve a project that I’m just now operating on,
    and I have been on the glance out for such information.

  5. fatal attraction netflix

    Fascinating blog! Is your theme custom made or did you download it from somewhere?
    A design like yours with a few simple adjustements would really make my blog shine.
    Please let me know where you got your theme. Bless you

  6. 메리트카지노

    Hello, i think that i saw you visited my site so i came to “return the
    favor”.I am trying to find things to enhance my site!I suppose its ok to use a few of your
    ideas!!

  7. 코인 카지노 사이트

    Ahaa, its good discussion about this paragraph here at this blog, I have
    read all that, so now me also commenting at this place.

  8. 스타 듀 밸리 카지노

    This paragraph is genuinely a nice one it helps new the web visitors, who are wishing in favor of blogging.

  9. 솔레어카지노

    Wow, awesome blog layout! How long have you been blogging for?
    you made blogging look easy. The overall look of your
    web site is magnificent, let alone the content!

  10. オンラインカジノ

    Wow, this paragraph is fastidious, my sister is analyzing these things, therefore I am going to let
    know her.

  11. 베트웨이

    Hello there! Do you know if they make any plugins
    to protect against hackers? I’m kinda paranoid about losing everything I’ve worked hard on. Any suggestions?

  12. 퍼스트카지노

    I do consider all of the ideas you’ve presented on your post.

    They are very convincing and will certainly work. Still, the posts are too quick
    for newbies. Could you please extend them a bit from next time?

    Thank you for the post.

  13. 시아누크빌카지노

    constantly i used to read smaller content that
    also clear their motive, and that is also happening with this paragraph which I am
    reading now.

  14. カジノ シークレット 出 金 条件

    Great post. I was checking continuously this blog and I am impressed!
    Extremely helpful info particularly the last part 🙂 I care for such info a lot.
    I was looking for this particular information for a long time.
    Thank you and good luck.

  15. online casinos

    Incredible points. Solid arguments. Keep up the good effort.

  16. 우리카지노

    magnificent issues altogether, you just gained
    a new reader. What could you recommend about your put
    up that you just made some days ago? Any certain?

  17. Dafabet

    It’s perfect time to make some plans for the future and it’s
    time to be happy. I’ve read this put up and if I could I
    want to suggest you some fascinating things or tips.
    Maybe you can write subsequent articles relating
    to this article. I desire to learn even more issues approximately it!

  18. play poker online

    Excellent post. I was checking constantly this weblog and I’m inspired!
    Extremely useful information specifically the closing part 🙂 I maintain such information much.
    I used to be seeking this certain info for a very
    lengthy time. Thanks and best of luck.

  19. washing machine sale

    Amazing issues here. I’m very glad to look your article.

    Thanks a lot and I am having a look forward to
    touch you. Will you kindly drop me a e-mail?

  20. 더킹카지노

    There’s definately a great deal to know about this topic.
    I love all the points you made.

  21. bắn cá ăn xu

    An outstanding share! I’ve just forwarded this onto a coworker who was doing a little
    homework on this. And he in fact ordered me breakfast because I discovered
    it for him… lol. So let me reword this…. Thanks for the meal!!
    But yeah, thanx for spending some time to talk about this topic here
    on your web site.

  22. カジ 旅 出 金 時間

    I’m extremely inspired with your writing talents as smartly as with the format to your
    blog. Is that this a paid topic or did you modify it yourself?
    Anyway keep up the excellent high quality
    writing, it’s rare to look a great weblog like this one nowadays..

  23. ワイルドジャングルカジノ 評価

    Pretty component to content. I just stumbled upon your website and in accession capital to say that I acquire actually loved account your blog
    posts. Anyway I will be subscribing for your feeds and even I success you get right of entry
    to persistently quickly.

  24. พนันกีฬา

    I just like the valuable information you supply in your articles.
    I will bookmark your weblog and test again right here frequently.

    I’m fairly sure I will learn plenty of new stuff right right
    here! Good luck for the following!

  25. 예스카지노

    There is definately a lot to know about this subject.
    I like all the points you have made.

  26. Titanium watch

    Hello everyone, it’s my first visit at this site, and piece of writing
    is genuinely fruitful designed for me, keep
    up posting these articles.

  27. 윌리엄힐

    Everyone loves it when people come together and share opinions.
    Great website, stick with it!

Leave a Reply

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