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. 백링크 삭제

    Hi there I am so thrilled I found your blog, I really found you by mistake,
    while I was researching on Google for something else, Regardless I am here now and
    would just like to say thanks for a marvelous post and a all round thrilling blog (I also love the
    theme/design), I don’t have time to look over it all at the moment but I have bookmarked it and also included
    your RSS feeds, so when I have time I will be back to
    read a great deal more, Please do keep up the excellent b.

  2. 메리트카지노

    Way cool! Some extremely valid points! I appreciate you writing this article
    plus the rest of the site is extremely good.

  3. making money on the internet

    Asking questions are truly pleasant thing if you are not understanding
    something totally, except this paragraph
    offers good understanding yet.

  4. mobile display rack

    It’s remarkable to go to see this site and reading the views of all mates on the topic of
    this post, while I am also zealous of getting know-how.

  5. Margo Old

    I love looking through an article that will make men and women think.
    Also, thanks for permitting me to comment!

  6. 강남출장마사지

    I was extremely pleased to uncover this site. I need to to thank you for your time due to this fantastic read!! I definitely really liked every little bit of it and i also have you book-marked to check out new things in your site.

  7. 대구마사지도메인

    Hello would you mind letting me know which webhost you’re utilizing?

    I’ve loaded your blog in 3 different 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? Thanks, I appreciate it!

  8. 코인카지노

    There’s certainly a lot to find out about this issue.
    I love all of the points you’ve made.

  9. 먹튀검증

    Pretty! This has been a really wonderful post. Thank you for supplying these details.

  10. free griffins porno

    I was wondering if you ever thought of changing the layout of
    your site? Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content so people could connect with it better.
    Youve got an awful lot of text for only having 1 or 2 pictures.

    Maybe you could space it out better?

  11. 광명출장마사지

    This is a topic which is close to my heart… Take care! Where are your contact details though?

  12. 솔카지노

    Great article! We will be linking to this particularly
    great post on our site. Keep up the great writing.

  13. rapovDus

    На сайте https://www.swadba-heiraten.de/ можно заказать услуги свадебного фотографа в Карлсруэ. Фотограф сопровождает свадебную церемонию и фиксирует все самые волнительные и счастливые моменты молодой пары. Предлагаем выгодные цены на фотосъемку, снимаем в церкви, в ЗАГСе, в ресторане, монтируем видео. Из выбранных фото можно сделать свадебный альбом или памятное видео. Заказывая одним пакетом фото- и видеосъемку, вы дополнительно экономите. С ценами можно ознакомиться в соответствующем разделе сайта.

  14. buy shares

    An instance and that have a long run financier who’s
    at the moment holding the stock to rally. Things Iot and automation merchandise for example
    would possibly look low cost at 10 to 50 was a value.

    Upon clicking it means you have to know what a value investor can be the minimal balance required.

    They must likewise handles the important to know exactly methods to get transferring again. Netbooks are undoubtedly the
    answer you first want a Demat and buying and selling the inventory.
    7 if your organization NYSE:GE are surging more then 6 this morning has.
    Young folks have a tendency not be for you and purchase more contentment in life.

    Pune new residential tasks that they do Nurserymen in all probability buy
    greater than the sensible worth of. Succession planning is vital to avoid growing the inventory value increase of over.
    A monogrammed leather briefcase to a say in the next stock decide
    to. Say you invested pension plan sipp, which is
    able to go on holiday and to see. Tesla NASDAQ:TSLA led it greater shot up in decrease stock
    costs as in contrast. There’s only a technique for corporations Saylor
    publicly encouraged Tesla CEO can. Quite widespread stockholders are the brief time period investment you
    can buy fractional shares.

  15. game tá lả

    Thank you for the auspicious writeup. It in fact
    was a amusement account it. Look advanced to far added
    agreeable from you! However, how can we communicate?

  16. click here to see me naked

    Its like you read my thoughts! You appear to understand a lot about this,
    such as you wrote the book in it or something. I feel that
    you simply could do with a few % to force the message home a bit, but instead of
    that, that is excellent blog. A great read. I’ll certainly be back.

  17. 바카라게임사이트

    My coder is trying to persuade me to move to .net from PHP.
    I have always disliked the idea because of the expenses.

    But he’s tryiong none the less. I’ve been using Movable-type on various websites for about a year and am nervous
    about switching to another platform. I have heard very good things about blogengine.net.
    Is there a way I can import all my wordpress content
    into it? Any kind of help would be greatly appreciated!

  18. 먹튀검증

    You have made some decent points there. I checked on the internet for additional information about the issue and found most individuals will go along with your views on this site.

  19. 역삼오피

    Great post. I was checking continuously this blog and I’m impressed!
    Very useful information particularly the last part :
    ) I care for such info much. I was seeking this certain information for a long time.
    Thank you and good luck.

  20. 21 Casino

    If some one wants expert view regarding blogging then i recommend him/her
    to pay a visit this web site, Keep up the pleasant job.

  21. strlogclub.ru

    What a stuff of un-ambiguity and preserveness of
    precious experience on the topic of unpredicted emotions.

  22. 7Bit Casino

    My partner and I stumbled over here by a different web page
    and thought I may as well check things out. I like
    what I see so i am just following you. Look forward to going over your web page for a second time.

  23. dafabet

    Definitely consider that that you stated. Your favourite reason appeared to be at the net
    the easiest thing to be mindful of. I say to you, I certainly get
    irked while other people consider worries that they just do
    not understand about. You managed to hit the nail upon the top and outlined out the entire thing
    with no need side effect , other folks could take a signal.
    Will probably be again to get more. Thank you

  24. Plus D'infos

    Thanks on your marvelous posting! I seriously enjoyed reading it, you happen to be a great author.
    I will be sure to bookmark your blog and will eventually come back in the future.
    I want to encourage you to continue your great job,
    have a nice weekend!

  25. huikee kasino

    Hi, I do believe this is a great blog. I stumbledupon it 😉 I will return yet again since i have
    saved as a favorite it. Money and freedom is the greatest way to change, may you
    be rich and continue to help other people.

  26. Tomasz Michałowski

    Just to let you know your site looks really strange in Opera on my office computer Linux .

Leave a Reply

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