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.

807 comments

  1. judgements

    Helpful info. Lucky me I discovered your web site accidentally, and I am surprised why this
    accident didn’t happened in advance! I bookmarked it.

  2. ortaca firma rehberi

    Do you have a spam problem on this blog; I also am a blogger,
    and I was wondering your situation; many of us have created some nice practices and we are looking
    to exchange techniques with others, please shoot me an e-mail if interested.

  3. 2 Line Poetry

    excellent submit, very informative. I’m wondering why the other
    specialists of this sector do not notice this. You should proceed your writing.
    I am sure, you’ve a great readers’ base already!

  4. continue reading this

    I am not sure where you’re getting your information, but good topic.
    I needs to spend some time learning more or understanding more.
    Thanks for wonderful information I was looking
    for this info for my mission.

  5. technology week blog news

    Hi there Dear, are you in fact visiting this web site regularly, if so afterward you will definitely obtain good
    know-how.

  6. digital signage

    What a material of un-ambiguity and preserveness of valuable familiarity about unpredicted feelings.

  7. dsa

    At this time it sounds like WordPress is the preferred blogging platform available right now.

    (from what I’ve read) Is that what you’re using on your blog?

  8. digital signage

    I’ve been exploring for a little for any high-quality articles or
    weblog posts in this kind of space . Exploring in Yahoo I finally stumbled upon this site.
    Reading this info So i’m glad to exhibit that I’ve an incredibly good
    uncanny feeling I found out exactly what I needed.
    I such a lot unquestionably will make certain to
    do not overlook this website and provides it a look
    on a constant basis.

  9. Technology week blog news

    Hi there! Do you know if they make any plugins to assist with Search Engine Optimization? I’m trying to get my blog to rank for
    some targeted keywords but I’m not seeing
    very good gains. If you know of any please share.

    Thanks!

  10. technology week

    I am extremely impressed with your writing skills as well as with the
    layout on your blog. Is this a paid theme or did you modify it yourself?
    Anyway keep up the nice quality writing, it’s rare to
    see a nice blog like this one today.

  11. Learn More Here

    Your mode of explaining the whole thing in this piece of writing is
    really good, all be capable of easily be aware of it, Thanks a lot.

  12. Omer

    Hello! I’m at work surfing around your blog from my new apple iphone!
    Just wanted to say I love reading through your blog and look
    forward to all your posts! Keep up the great
    work!

  13. Zack

    This website was… how do you say it? Relevant!!
    Finally I have found something that helped me.
    Thanks a lot!

  14. adult shop

    You’ve made some really good points there. I looked on the net to learn more about the issue
    and found most people will go along with your views on this site.

  15. Claude Pokrzywa

    Whether you’re planning an exotic journey or going for a spur in the minute jaunt, sometimes, it’s the little information which can be the deciding component regarding how a lot you prefer your time out of the house. The information in this post will assist you to strategy the right escape. Keep all pointless valuables in your own home. You are taking the health risks of getting every little thing robbed on your part if you take belongings along with you. Should you be going to a place which has a liquefied-constraint on all drinks you may well be packaging, put money into bar hair shampoos and teeth natural powder. Amazingly, you can easily locate nightclub hair shampoo and toothpaste obtainable in natural powder develop on the internet. These materials are a fantastic way to obtain all around liquefied-restrictions. When you are traveling in countries with dangerous faucet water, keep in mind alternative methods which you might be revealed. Close up the mouth while using the shower and remember to brush your pearly whites just with treated h2o. If you make teas or gourmet coffee using the normal water, allow it to boil for a lot of moments just before steeping. Also a small coverage could make you extremely ill. Purchase a vacation dress, which is a garment that could be donned several techniques. It can be worn being a gown, skirt, tee shirt and wrap. You can then load up a number of other extras and related items, that can save a great deal of space with your suitcase for all of the mementos you want to bring property. Acquire a number of clothespins together with you once you traveling. It is really an unconventional product to take into consideration packaging, nonetheless they may be really valuable. If you’re going on a cruise trip, make sure you bring a small working day travelling bag together with you. You’ll discover that your travel luggage won’t be available right away whenever you table the deliver. So you’ll wish to have a case with a go swimming fit, a novel, an additional change of garments inside, and anything else you may want right away. Wherever you want to go, preparation is likely to make your trip all it may be. Utilize the suggestions you’ve read through right here to get the most out of your vacationing encounter.

  16. robux generator

    Now I am going to do my breakfast, after having my breakfast coming again to read additional news.

  17. Sherill Whitler

    Whether or not you’re preparation an exotic vacation or having a spur of the moment jaunt, occasionally, it’s the small particulars which can be the choosing aspect regarding how a lot you like your time and energy out and about. The data in the following paragraphs can help you program an ideal vacation. Depart all unnecessary possessions at home. You take the hazards of having almost everything thieved of your stuff if you do provide valuables along with you. If you are planing a trip to an area that includes a fluid-limitation on all liquids you may be packing, invest in pub hair shampoos and tooth natural powder. Surprisingly, you can easily locate bar hair shampoo and tooth paste for sale in natural powder develop on the internet. These things are a fun way to have around water-limits. When you are traveling in countries around the world with harmful tap water, keep in mind other ways which you might be exposed. Close the mouth while using the bath and remember to brush your teeth only with treated drinking water. If you make green tea or gourmet coffee together with the normal water, allow it to boil for many minutes or so before steeping. A good little visibility will make you quite sickly. Purchase a travel gown, which is actually a garment which can be donned multiple methods. It might be donned as being a attire, skirt, shirt and wrap. You can then load up a number of other add-ons and associated goods, that will help save plenty of area inside your travel suitcase for all of the souvenirs you intend to deliver house. Acquire a few clothespins along if you travel. It is really an unusual object to consider loading, however they could be rather beneficial. If you’re going on a vacation cruise, be sure you bring a tiny day time handbag together with you. You’ll realize that your baggage won’t be around right away once you board the ship. So you’ll want a handbag by using a swim go well with, a magazine, an additional change of clothes within it, and whatever else you may want right away. Wherever you need to go, planning will make your trip all it could be. Take advantage of the advice you’ve read here to get the most out of your vacationing encounter.

  18. 구글상위노출프로그램

    Hey There. I found your blog the usage of msn. This is a really neatly written article.
    I will be sure to bookmark it and come back to read more of your
    useful information. Thank you for the post. I’ll certainly return.

    Here is my blog post – 구글상위노출프로그램

  19. pills to build muscle fast

    I’m really enjoying the theme/design of your site. Do you ever run into any
    web browser compatibility issues? A handful of my blog visitors have complained about
    my website not working correctly in Explorer but looks great in Opera.
    Do you have any tips to help fix this issue?

  20. click for more info

    I’d personally wish to be some sort of part of group wherever I can get suggestions from the other experienced many people of which share the identical interest. In the event that you have any tips, please let me recognize. Thank you.

  21. best spinning software

    With havin so much content do you ever run into any issues of plagorism or
    copyright violation? My website has a lot of exclusive content I’ve
    either created myself or outsourced but it seems a lot of it is popping
    it up all over the web without my agreement. Do you know any techniques to help stop content from being ripped off?
    I’d certainly appreciate it.

  22. Afton Cutrer

    No matter if you’re planning an exotic journey or having a spur from the minute jaunt, at times, it’s the tiny details that could be the choosing element regarding how very much you prefer your time out of the house. The data in this post will allow you to program the ideal escape. Leave all needless valuables in your house. You are taking the potential risks of having every thing robbed from you should you bring valuables along. If you are visiting an area that has a fluid-restriction on all beverages you might be preparing, spend money on bar hair shampoos and teeth powder. Surprisingly, it is possible to find bar shampoo or conditioner and tooth paste offered in powder form on-line. These things are a great way to acquire all around fluid-constraints. When traveling in countries with unsafe regular faucet water, keep in mind other methods that you might be uncovered. Close up your mouth while using the shower and clean your tooth just with dealt with h2o. If one makes herbal tea or coffee together with the drinking water, give it time to boil for a number of a few minutes well before steeping. A good modest exposure could make you quite sickly. Get a traveling outfit, and that is a outfit that could be put on numerous techniques. It might be used like a outfit, skirt, tee shirt and place. Then you can load up several other add-ons and associated products, that will preserve a lot of area in your luggage for the mementos you intend to bring home. Get a few clothespins along when you traveling. It is really an unusual product to think about loading, nevertheless they could be quite beneficial. If you’re taking a vacation cruise, ensure you bring a compact working day bag along with you. You’ll discover that your suitcases won’t be around without delay whenever you board the ship. So you’ll wish to have a travelling bag using a go swimming fit, a guide, an extra modify of clothes in it, and everything else you may need straight away. Irrespective of where you would like to go, preparation can make your holiday all it could be. Make use of the suggestions you’ve go through in this article to acquire the most out of your touring practical experience.

  23. best spinner software

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

Leave a Reply

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