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,097 comments

  1. green lamp bloomingville

    Hi there! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community
    in the same niche. Your blog provided us valuable
    information to work on. You have done a wonderful job!

  2. 우리카지노

    I’ve been brokwsing online more than three hours today, yet I never found any interresting article like
    yours. It’s pretty worth enough for me. Personally, if all site owneds and bloggers
    made good ontent as you did, the internet will be uch more useful
    thann ever before.

    My webpage :: 우리카지노

  3. Joker123 Deposit

    Good day! I know this is kinda off topic nevertheless I’d figured I’d ask.

    Would you be interested in trading links or maybe guest authoring a blog post
    or vice-versa? My website discusses a lot of the same topics as yours
    and I believe we could greatly benefit from each other.
    If you happen to be interested feel free to shoot me an e-mail.
    I look forward to hearing from you! Fantastic blog by the way!

  4. 온라인바둑이

    Why people still make use of to read news papers
    when in this technological world the whole thing is presented on net?

    Feel free to surf to my web page … 온라인바둑이

  5. https://sites.google.com/view/perch-light-tischleuchte

    Excellent article. Keep writing such kind of information on your page.
    Im really impressed by your blog.
    Hi there, You’ve done a great job. I will definitely digg it and
    in my opinion suggest to my friends. I am confident they’ll be benefited from this website.

  6. 온라인바둑이

    Useful information. Lucky me I discovered your site unintentionally,
    and I’m stunned why this coincidence did not took place in advance!
    I bookmarked it.

    Also visit my web page 온라인바둑이

  7. binary options in india

    I blog quite often and I truly appreciate your content.
    Your article has truly peaked my interest.
    I’m going to take a note of your blog and keep checking for new details about once per week.

    I subscribed to your RSS feed as well.

  8. 카지노

    Heya! I’m at work browsing your blog from my new iphone!

    Just wanted to say I love reading your blog and look forward to all your posts!
    Carry on the outstanding work!

  9. 빅카지노

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

  10. loose diamonds make

    Hello there! This article could not be written much better!
    Reading through this article reminds me of my previous roommate!
    He continually kept talking about this.
    I will send this post to him. Fairly certain he’ll have a very good
    read. Thank you for sharing!

  11. 솔레어카지노

    We are a group of volunteers and starting a new scheme in our community.
    Your site offered us with valuable info to work on. You’ve done an impressive job and our entire
    community will be grateful to you.

  12. 대구출장 달리기

    Wow, this piece of writing is nice, my sister is analyzing these kinds of things, thus I am going to tell her.

  13. Wild Jungle Casino

    You made some really good points there. I checked on the
    web for more info about the issue and found most people will go along with your views on this website.

  14. cờ tướng úp miễn phí

    What’s up to every body, it’s my first visit of this blog; this webpage includes remarkable and actually fine data for readers.

  15. オンラインカジノ

    First of all I want to say wonderful blog! I had a quick question that I’d like to ask if you
    do not mind. I was curious to find out how you center yourself
    and clear your thoughts prior to writing. I’ve had
    a hard time clearing my mind in getting my thoughts
    out there. I do enjoy writing however it just seems like the first 10
    to 15 minutes are usually wasted simply just trying
    to figure out how to begin. Any recommendations or hints?
    Thank you!

  16. dealer license

    When someone writes an post he/she retains the image of a user in his/her mind that how a user can understand
    it. So that’s why this article is outstdanding. Thanks!

  17. 대구업소 바로가기

    I’ve been browsing on-line greater than three hours these days, but I by no means discovered any
    fascinating article like yours. It’s beautiful price enough for
    me. In my view, if all web owners and bloggers made just right
    content as you probably did, the internet
    might be a lot more useful than ever before.

  18. Mellissa

    I always emailed this weblog post page to all
    my contacts, as if like to read it next my links will
    too.

  19. http://159.89.228.97/

    Excellent post. I was checking continuously this weblog and I am impressed!
    Very useful information particularly the closing section 🙂
    I handle such info a lot. I was seeking this certain information for a very lengthy time.
    Thanks and good luck.

  20. ラブドール

    Very well written information. It will be useful to everyone who usess it, including myself. Keep doing what you are doing – looking forward to more posts. ラブドール 巨乳

  21. cdl classes

    Heya i am for the primary time here. I found this board and I to find It really helpful & it helped me out a lot.

    I hope to give something back and aid others like you aided me.

  22. security camera installation

    I was recommended this web site by my cousin. I’m not sure whether this post is
    written by him as no one else know such detailed about my problem.
    You’re amazing! Thanks!

  23. rkydfRix

    “Приоритет” – детективное агентство, которое предоставляет профессиональные услуги частного сыска в Санкт-Петербурге. Клиенты, которые подозревают супруга в измене, хотят собрать информацию или найти конкретного человека, могут обратиться к частным детективам в Санкт-Петербурге. Компания предоставляет услуги частным лицам и бизнесу, гарантирует полную конфиденциальность и эффективное решение. По ссылке https://spb-detective.ru/ можно детально ознакомиться с услугами, узнать цены и оставить заявку.

  24. 빅카지노

    When someone writes an post he/she retains the thought
    of a user in his/her brain that how a user can understand it.

    Therefore that’s why this post is great. Thanks!

Leave a Reply

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