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.

806 comments

  1. Agate mousse perles

    If some one needs expert view on the topic of running a blog after that i recommend him/her to pay a quick visit this blog,
    Keep up the fastidious job.

  2. Christopher

    Nice replies in return of this matter with solid
    arguments and explaining everything regarding that.

  3. howlite perle

    It is appropriate time to make some plans for the longer term and it is time to be happy.
    I have read this post and if I may just I wish to
    suggest you few interesting issues or advice. Perhaps you can write next articles
    referring to this article. I desire to learn even more issues approximately it!

  4. Nicole

    Its like you read my mind! You appear to know a lot about this, like
    you wrote the book in it or something. I think that you could do with a few pics to drive the message home a
    bit, but instead of that, this is wonderful blog.
    A great read. I’ll definitely be back.

  5. Verna

    I think this is one of the such a lot significant info for me.
    And i am glad studying your article. However should statement on few normal issues, The site taste is great, the
    articles is actually great : D. Excellent task, cheers

  6. apatite

    This website was… how do I say it? Relevant!! Finally
    I have found something which helped me. Kudos!

  7. 파라오카지노

    Hi I am so glad I found your web site, I really found you by mistake, while I was browsing on Digg for something else, Anyhow I am here now and would
    just like to say cheers for a incredible post and a
    all round exciting blog (I also love the theme/design), I don’t have time to go through
    it all at the minute but I have saved it and also added your RSS feeds, so when I have time I will be
    back to read more, Please do keep up the fantastic job.파라오카지노

  8. Marco Orizetti

    You don’t need a website! They are useless at selling and making money you online. If you are just getting started I recommend getting hold of a done for you funnel. There are plently around… I have used 3 different funnels I grabbed for free and all of them have made me money. My favourite though would have to be the Super Funnel Hero! by George Wickens. It’s very slick!

  9. 먹튀검증

    My family members always say that I am killing my time here at net, however I know I am getting knowledge every
    day by reading such nice posts.

  10. link indexer

    Excellent, what a website it is! This website
    presents helpful information to us, keep it up.

  11. Percy Femmer

    Travelling is fascinating and could be a terrific time for yourself or one of the primary headaches you will at any time deal with or else done efficiently. Read on for several fantastic tips on how to traveling smart and care for all of the small things that when you don’t, will create wanting you stayed residence. Check out choice spots to keep. You don’t always have to remain in a resort to feel good on vacation. There are lots of “house-swapping” web sites available, which allow you to remain in someones empty house. Look for cabins or bed and breakfasts. Different lodging might be some of the most intriquing, notable and exciting areas of vacationing, so make sure to take a look! When you are traveling within a strict budget, make use of the in-space services to prepare your personal food. The coffeemaker that is certainly typically offered in even lowest priced hotel rooms can double as a warm plate. You can warmth simple water from the carafe for ramen or soups, or use the very hot platter on its own to create sausage. Think about traveling to modest municipalities when you are on a budget. Modest municipalities have their very own special elegance and attractions. You can generally find traditional zones and attractive scenery around. Lodgings tend to be very reasonable. Because the town is not a vacationer destination, this is a great option for a calm retreat. Do your homework in advance and search for dining credits and coupon codes for your personal spot. You may also help save a lot of cash by obtaining assistance with where you should eat before you decide to leave. This is preferable to asking for recommendations if you are previously eager as well as in a strange place. Vacation can be extremely fulfilling. Everyone, of several age groups, competitions and creeds can learn and grow from a journey practical experience. There are several resources on the market to aid the unseasoned traveler. Just a little preparing can go a long way and traveling is a pleasure, not much of a source of nervousness.

  12. leguma

    Generally I don’t learn post oon blogs, however I wissh too say that
    this write-up vety forced me to take a look at and do
    so! Your writing style has been amazed me. Thank you, very great post.

    Feeel free to sudf to my webpage – leguma

  13. Usha Kerwin

    I believe this is among the such a lot significant information for me. And i’m satisfied reading your article. But want to observation on few normal things, The site style is great, the articles is truly excellent : D. Good task, cheers

  14. Kim Parnell

    you’re really a good webmaster. The website loading speed is incredible. It seems that you’re doing any unique trick. Also, The contents are masterpiece. you have done a fantastic job on this topic!

  15. web site

    Excellent way of telling, and fastidious article too take data concerninng my presentation subject
    matter, which i amm going to deliver in academy.
    web site

  16. Kevin David

    Iíve recently started a web site, the information you offer on this web site has helped me tremendously. Thanks for all of your time & work.

  17. ladyship's

    Hi, its pleasant paragraph regarding media print, we all understand media is
    a great source of information.

  18. penal

    Every weekend i used to pay a visit this web site, for the reason that i wish for enjoyment, for the reason that this this
    web page conations actually nice funny stuff too.

  19. Audible review for 2020 is membership Truly worth it?

    How Can Audible Function?
    You take out a free trial of Audible and receive a complimentary audiobook.
    This could be among perhaps an originals or the classics.

    At the end of the trial, you can buy a subscription of Audible.
    You will need to register for the membership using your Amazon account.

    Monthly, Audible awards you. You can take advantage of this credit to
    purchase Audible audio books in different categories including technology, style, love, social
    networking, ads, etc..
    If you would like to purchase books, more Audible
    credits can be bought by you or pay per audio publication.
    Interestingly, a member can download 2 of six star Originals about the first Friday of every month.
    They do not charge any credits. It is possible to keep
    these Audible Originals .
    You have Audible audio books on your library even in the event you cancel your subscription.
    It’s possible to listen to Audible audio books anyplace using apps on your mobile, Windows or Mac computer
    or Alexa device.|
    What is Audible?
    With more than 300,000 names to its title, Audible is the world’s biggest seller and manufacturer of audiobooks.

    Audible Review https://heavenenvoy.mn/amazon-com-presents-free-of-cost-one-month-audible-membership/

Leave a Reply

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