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.

10,937 comments

  1. 코인카지노

    Link exchange is nothing else however it is simply placing
    the other person’s webpage link on your page at proper place and other person will also do similar in favor of you.

  2. 7Bit Casino

    Hello, its pleasant post on the topic of media print, we all be familiar
    with media is a impressive source of facts.

  3. 넷마블 바카라

    Hello, for all time i used to check website posts here in the early hours in the break of day, since i
    like to find out more and more.

  4. Titanium Trim

    Hello, I enjoy reading all of your post. I like to write a little comment to support you.

  5. ポーカー 確率

    Hmm is anyone else having 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 feed-back would be greatly appreciated.

  6. royal panda india

    Because the admin of this web site is working, no question very shortly it will be well-known, due
    to its feature contents.

  7. m88 mobile

    Just want to say your article is as surprising.

    The clearness in your post is simply spectacular
    and i could assume you are an expert on this subject. Well with your permission allow me to grab your RSS feed
    to keep up to date with forthcoming post. Thanks
    a million and please carry on the enjoyable work.

  8. 엠 카지노 쿠폰

    You really make it seem so easy with your presentation but
    I find this matter 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 will try to get the hang of it!

  9. オンラインカジノ 日本語

    I know this if off topic but I’m looking into starting my own weblog and was wondering what all is required to get setup?

    I’m assuming having a blog like yours would cost a pretty penny?
    I’m not very internet smart so I’m not 100% positive.
    Any recommendations or advice would be greatly appreciated.
    Thank you

  10. sonos one speaker

    It’s going to be finish of mine day, however before ending I
    am reading this impressive post to improve my know-how.

  11. ライブ カジノ ハウス 登録

    Good day! Do you know if they make any plugins
    to help with SEO? I’m trying to get my blog to rank for some targeted keywords
    but I’m not seeing very good results. If you know of any please share.
    Thank you!

  12. 카지노사이트

    Your style is very unique compared to other people I have read stuff from.
    Thanks for posting when you’ve got the opportunity, Guess I’ll just bookmark this page.

  13. zebra technologies

    My partner and I absolutely love your blog and find many of your
    post’s to be exactly I’m looking for. Do you offer guest writers to
    write content for yourself? I wouldn’t mind publishing a post or elaborating on a few
    of the subjects you write regarding here. Again, awesome site!

  14. 소녀 blowjob

    Superb blog! Do you have any suggestions for aspiring writers?
    I’m hoping to start my own blog soon but I’m a little lost on everything.
    Would you suggest starting with a free platform like WordPress or go for a paid option? There are so
    many choices out there that I’m totally overwhelmed .. Any ideas?
    Bless you!

  15. lifetime swim lessons

    Its like you read my mind! You seem to know so
    much about this, like you wrote the book in it or something.
    I think that you could do with some pics to drive the message home a
    bit, but instead of that, this is fantastic blog.
    A fantastic read. I’ll certainly be back.

  16. 코인카지노

    Good day! This post couldn’t be written any better!
    Reading through this post reminds me of my good old room mate!

    He always kept chatting about this. I will forward this post to
    him. Fairly certain he will have a good read.
    Thank you for sharing!

  17. 우리카지노

    Hello there! I could have sworn I’ve visited this site before but after looking
    at some of the articles I realized it’s new to me. Regardless,
    I’m definitely happy I found it and I’ll be bookmarking it and checking back often!

  18. 카지노사이트

    Nice post. I was checking continuously this blog and I
    am inspired! Extremely useful information specifically the last section 🙂 I handle such info much.

    I used to be seeking this particular info for a very lengthy time.

    Thanks and best of luck.

  19. die cutting machine

    Excellent post. Keep posting such kind of info on your page.
    Im really impressed by your blog.
    Hi there, You’ve done a great job. I will certainly
    digg it and in my opinion recommend to my friends. I am confident they’ll be
    benefited from this web site.

  20. pti transportation

    Hello friends, fastidious article and pleasant arguments commented here, I am really enjoying by these.

  21. v9bet sportsbook

    I do not even know how I ended up here, but I thought this post was good.
    I don’t know who you are but definitely you are going to
    a famous blogger if you aren’t already 😉 Cheers!

  22. 바카라사이트

    It is the best time to make some plans for the future and it’s time
    to be happy. I have read this post and if I could I wish to
    suggest you few interesting things or tips. Perhaps
    you can write next articles referring to this article.

    I wish to read even more things about it!

  23. カジノゲーム

    Hmm it seems like your website ate my first comment (it was
    extremely long) so I guess I’ll just sum it up what I wrote and say, I’m thoroughly enjoying your blog.
    I as well am an aspiring blog writer but I’m still new to the
    whole thing. Do you have any recommendations for first-time blog writers?
    I’d really appreciate it.

  24. lotto vip

    Hello, I enjoy reading through your post. I wanted to write a little
    comment to support you.

  25. 더나인카지노

    Howdy! This blog post couldn’t be written much better!
    Looking at this article reminds me of my previous roommate!
    He always kept preaching about this. I most certainly will forward this post to him.
    Pretty sure he’ll have a very good read. I appreciate you for sharing!

  26. 카지노 게임 우리 카지노

    An impressive share! I’ve just forwarded this onto a colleague who has been conducting a little research on this.
    And he in fact bought me lunch simply because I stumbled upon it for him…
    lol. So let me reword this…. Thanks for the meal!! But yeah, thanks for spending some time to discuss this issue here on your web page.

  27. newspaper near me

    This design is steller! You definitely know how to keep a reader entertained.
    Between your wit and your videos, I was almost moved to
    start my own blog (well, almost…HaHa!) Great job.
    I really enjoyed what you had to say, and more than that, how you
    presented it. Too cool!

  28. microsoft project plan

    It’s nearly impossible to find well-informed people
    in this particular subject, but you sound like you know what you’re talking about!

    Thanks

Leave a Reply

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