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.

9,091 comments

  1. 코인카지노

    Your style is very unique in comparison to other folks I’ve read stuff from.
    Thank you for posting when you have the opportunity, Guess I’ll just bookmark
    this web site.

  2. 1xBet

    Hello there, just became aware of your blog through Google, and found that it is really informative.

    I’m going to watch out for brussels. I’ll be grateful
    if you continue this in future. Many people will be benefited from your writing.

    Cheers!

  3. web page

    It’s really a great and useful piece of info. I amm happy that you
    simply shared this helpful info with us. Please keep uss
    informesd like this.Thank yoou for sharing.
    web page

  4. buy cvv 2021

    buy cvv Good validity rate Sell Make good job for you Pay
    in site activate your card now for international transactions.

    ————-CONTACT———————–
    WEBSITE : >>>>>> Cvvgood✺ Shop

    —– HERE COMES THE PRICE LIST ———–
    ***** CCV US:
    – US MASTER CARD = $2,3 per 1 (buy >5 with price $3 per 1).

    – US VISA CARD = $2,4 per 1 (buy >5 with price $2.5 per 1).

    – US AMEX CARD = $4 per 1 (buy >5 with price $2.5 per 1).

    – US DISCOVER CARD = $2,6 per 1 (buy >5 with price $3.5 per 1).

    – US CARD WITH DOB = $15 per 1 (buy >5 with price $12 per 1).

    – US FULLZ INFO = $40 per 1 (buy >10 with price $30 per 1).

    ***** CCV UK:
    – UK CARD NORMAL = $2,4 per 1 (buy >5 with price $3 per 1).

    – UK MASTER CARD = $2,2 per 1 (buy >5 with price $2.5 per
    1).
    – UK VISA CARD = $3,5 per 1 (buy >5 with price $2.5 per 1).

    – UK AMEX CARD = $4,4 per 1 (buy >5 with price $4 per 1).
    $3

    – UK CARD WITH DOB = $15 per 1 (buy >5 with price $14 per 1).

    – UK WITH BIN = $10 per 1 (buy >5 with price $9 per
    1).
    – UK WITH BIN WITH DOB = $25 per 1 (buy >20 with price $22 per 1).

    – UK FULLZ INFO = $40 per 1 (buy >10 with price $35 per 1).

    ***** CCV AU:
    – AU MASTER CARD = $5.5 per 1 (buy >5 with price $5 per 1).

    – AU VISA CARD = $5.5 per 1 (buy >5 with price $5 per 1).

    – AU AMEX CARD = $8.5 per 1 (buy >5 with price $8 per 1).

    – AU DISCOVER CARD = $8.5 per 1 (buy >5 with price $8 per 1).

    ***** CCV CA:
    – CA MASTER CARD = $6 per 1 (buy >5 with price $5 per 1).

    – CA VISA CARD = $6 per 1 (buy >5 with price $5 per 1).

    – CA VISA BUSINESS = $14 per 1 (buy >5 with price $13 per 1).

  5. w88 best

    bookmarked!!, I love your website!

  6. Bev

    If some one wishes expert view on the topic of blogging then i
    recommend him/her to go to see this blog, Keep up the good work.

    Here is my web site … Bev

  7. 카지노사이트

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

  8. 카지노사이트

    This design is steller! You certainly know how to keep a
    reader amused. 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!

  9. 더킹카지노

    Hi, i believe that i saw you visited my website so i came to go back the desire?.I’m attempting to to find things to improve my website!I suppose its good enough to use a few of your concepts!!

  10. البريد السريع الدولي

    Have you ever considered about including a little bit more
    than just your articles? I mean, what you say is important and all.
    Nevertheless think of if you added some great images or videos to give your posts
    more, “pop”! Your content is excellent but with images and videos, this blog could
    certainly be one of the greatest in its field. Awesome blog!

  11. 빅카지노

    I loved as much as you’ll receive carried out right here.
    The sketch is tasteful, your authored subject matter stylish.
    nonetheless, you command get bought an impatience over that you wish be delivering the following.
    unwell unquestionably come more formerly again as exactly the same nearly very often inside case you shield this hike.

  12. 우리카지노

    An impressive share! I have just forwarded this onto a friend
    who was conducting a little homework on this. And he in fact ordered me
    dinner simply because I stumbled upon it for him…

    lol. So let me reword this…. Thank YOU for the meal!! But yeah, thanks for spending the
    time to discuss this subject here on your web page.

  13. Buy Facebook shares

    Everything is very open with a clear clarification of the issues.
    It was truly informative. Your site is very helpful.
    Many thanks for sharing!

  14. fake bonsai tree

    Appreciating the persistence you put into your blog and
    detailed information you offer. It’s awesome to come
    across a blog every once in a while that isn’t the same unwanted rehashed information. Great read!
    I’ve saved your site and I’m adding your RSS feeds to my Google account.

  15. letou

    Attractive section of content. I just stumbled upon your website and in accession capital to
    assert that I get actually enjoyed account your blog posts.

    Anyway I will be subscribing to your feeds and
    even I achievement you access consistently rapidly.

  16. 텍사스홀덤

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

    Also visit my blog; 텍사스홀덤

  17. family lawyers London Ontario

    Hi there to every single one, it’s genuinely
    a fastidious for me to pay a visit this web page, it includes useful Information.

  18. 게이

    I do not even understand how I finished up right here, however I
    thought this submit was great. I do not understand who
    you are however definitely you are going to a well-known blogger should you are not already.
    Cheers!

  19. 더킹 카지노

    When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get four
    e-mails with the same comment. Is there any way you can remove people
    from that service? Thank you!

  20. 빅카지노

    Good day! Would you mind if I share your blog with my myspace group?
    There’s a lot of people that I think would really appreciate your content.
    Please let me know. Thank you

  21. pcceeZoofe

    Опытные педагоги на сайте https://funny-show.ru/ предоставляют качественную помощь для каждого заинтересованного ребенка. Творческое развитие началось с научного шоу. Открытие собственного дела в данной сфере послужило новой стадией развития – с тех пор, была приобретена франшиза и деятельность была организована в двух разных городах. Благодаря накопленному опыту, развитие стало непрерывным, что положительным образом сказалось на развитии подрастающего поколения.

  22. 리얼돌

    I’ve been exploring for a little bit for any high-quality articles or weblog posts on this kind of
    area . Exploring in Yahoo I at last stumbled upon this website.
    Reading this info So i am glad to show that I’ve
    an incredibly good uncanny feeling I discovered exactly what I needed.
    I most unquestionably will make certain to do not omit this website
    and give it a look regularly.

  23. plaquenil shortage

    Hi! I just wanted to ask if you ever have any problems with hackers?

    My last blog (wordpress) was hacked and I ended up losing months of hard work
    due to no data backup. Do you have any solutions to
    prevent hackers? http://herreramedical.org/chloroquine

  24. 더나인카지노

    Hey there! I could have sworn I’ve been to this
    site before but after browsing through some of the post
    I realized it’s new to me. Anyways, I’m definitely happy I found it and I’ll be book-marking and checking
    back often!

  25. Erica Sievert

    Your excellent know-how and kindness in playing with every one of the pieces ended up really practical. I don’t determine what I would’ve finished if I’d not encountered this kind of move like this.

Leave a Reply

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