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.

5,562 comments

  1. chaterba

    free porn cams chatroom with cam nude females with big tits rihanna sexy video

  2. online slots

    We can see a terrific quantity of decision for cricket fans in the below
    betting odds comparison table.

  3. glove box

    Good day! I know this is somewhat off topic but I was wondering which
    blog platform are you using for this site? I’m getting tired of
    Wordpress because I’ve had problems with hackers and I’m looking at options for another platform.
    I would be great if you could point me in the direction of a good platform.

  4. Nichwell glove box

    Very nice post. I just stumbled upon your weblog and wanted to say that
    I’ve truly enjoyed surfing around your blog posts. In any case I’ll be subscribing to your feed and I hope you write again soon!

  5. next online casino

    Join our e mail list and get special provides and discounts direct to your inbox.

  6. online sportsbook sites

    In 1976, Sega released an early combat sport game, Heavyweight Champ, primarily based
    on boxing and now regarded as the initial fighting game.

  7. betting on sports online

    When he’s not handicapping and watching sports, he enjoys golfing in the warmer months.

  8. Mickie

    You need to take part in a contest for one of the most useful sites
    online. I’m going to recommend this site!

  9. 토렌트 영화 추천

    You are so interesting! I do not believe I have read through anything like this before.

    So great to discover another person with a few original thoughts on this subject matter.
    Really.. thank you for starting this up. This web site is something that’s needed on the internet, someone with a bit of
    originality!

  10. poker online

    hi!,I love your writing so much! share we communicate more approximately your article on AOL?

    I need an expert on this house to resolve my problem. Maybe that is you!

    Having a look ahead to see you.

  11. 한국 토렌트 사이트

    Hi! I’m at work browsing your blog from my new iphone 3gs!
    Just wanted to say I love reading through your blog and look forward to all your posts!
    Carry on the fantastic work!

  12. nfl betting

    Ball State is 7- ATS in their final seven games when playing as the underdog.

  13. poker online

    hello there and thank you for your info – I’ve certainly picked up anything
    new from right here. I did however expertise several technical
    issues using this website, as I experienced to reload the
    site many times previous to I could get it to load properly.
    I had been wondering if your hosting is OK? Not that
    I am complaining, but slow loading instances times
    will often affect your placement in google and could damage your high quality score if ads and marketing with
    Adwords. Anyway I’m adding this RSS to my email
    and could look out for a lot more of your respective exciting
    content. Make sure you update this again soon.

  14. glove box

    Your mode of explaining everything in this article is in fact fastidious, all be able to effortlessly understand
    it, Thanks a lot.

  15. baseball odds

    If the service ends with a “.com,” it’s more most likely
    a reliable and legit sportsbook.

  16. betting tips

    E.g. a $100 bet on odds of +250 carries potential winnings of
    $250.

  17. teen sex gif

    kızlık bozma onlıne izle school giril amator sex videos
    rus pornosu sex videosu izle son yılların en sexi götleri gerdek gecesi kızlık zarı patlatma
    videoları azeri sekis ru , rus sekisi viyosu , rusya porno vidosu , rusya sex com …

  18. powerball online play sa

    We do list bookies on right here that may well not be readily available or legal in your
    nation.

  19. usa online casino

    Darcy is our go-to individual when we need to make sense of neighborhood regulation across Europe and neighbouring regions.

  20. onbling casino

    Rue, with an odds of 60-1, will have roughly a 1/61 likelihood of winning the games.

  21. arbitrage sports betting

    Many of our greatest tipsters are football fanatics, with a keen eye for betting value.

Leave a Reply

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