How to Add Leading Zeros in Excel

There are few default rules in Excel which can annoy you.

One of those rules is you can’t add a zero before a number.

The reason behind this is:

A zero before a number makes no sense and no value to the number itself.

Whenever you try to insert a zero before a number, Excel removes it...

...and you’ll only get the number starting from an actual number.

Now the thing is:

There are some unique situations where we need to add a zero before a number and we want that zero should stay there.

For example, phone number, zip codes, and invoice numbers, etc.

So today in this post, I’d like to share with you 5 different methods which we can use to add leading zeros in Excel.

Top 5 Ways to Add Leading Zeros in Excel

top excel tips tricks which can make you a pro this year

1. Convert a Number into a Text for Adding Zeros

Here is a thing...

...if you change the format of a cell from number to text then you can add a leading zero without any problem.

This way Excel will not treat your number as an actual number and will not delete any zero from it.

Here's how to do this...

  • Select the cells in which you want to add the leading zeros.
  • Go to Home ➜ Number ➜ Change the number format to text.
Using Text Format To Add Leading Zeros In Excel Before a Number

Now you can easily add zeros at the starting of the numbers.

Alert: You can't use these numbers further in any type of calculation because they are now in text format.

2. Use Concatenate Function to combine a Zero

If you want to insert a fixed numbers of zeros before a number, you can use concatenate function.

With CONCATENATE function... are combining zeros with actual numbers.

You can use a formula like below to add a zero before numbers in a cell.


Using Concatenate Function To Add Leading Zeros In Excel Before a Number

Alert: You can't use these numbers further in any type of calculation because they are now in text format.

3. Add Dynamic Leading Zeros with REPT Function

Using REPT Function To Add Leading Zeros In Excel Before a Number


In the above example, we have used REPT to get total 8 digits in a cell.

If a cell has 6 numbers then rest 2 digits will be zero...

...and if a cell has 4 numbers then rest of the 4 numbers will zero.

This is a dynamic formula to get zero before the numbers.

Alert: You can't use these numbers further in any type of calculation because they are now in text format

4. Use TEXT Function to Add Leading Zeros Format

You can also use TEXT function to add leading zeros.

TEXT Function will give you the same result as you can get by using text format.


Using Text Function To Add Leading Zeros In Excel Before a Number

In above example, we have converted a number into a text in which you will always get 7 digits.

If you have 5 digits are numbers rest of the 2 will be zeros at the start.

In text function, you just have to refer to the cell which has the value...

...and then the format as I have specified in above example.

Note: You can't use these numbers in any type of calculation because they are now in the text format.

5. Apply Custom Format with Leading Zeros

If you don't want to use any formula and all other methods, you can apply a custom format to a cell. 

I believe this is the best method to use...

...and it has an additional benefit over all the other methods.

using custom format to add leading zeros in excel
  • Select the cell in which you want to add zeros as a prefix.
  • Right click ➜ Format Option or you can also use the shortcut key Ctrl + 1 open "Format Cell Dialog Box".
  • In Category ➜ Select Custom.
  • In custom input dialog box, enter “0000000”.
  • Click OK.

In the above example, we have format the cells with 7 digit number in which you will always get 7 digits. If you have 5 digits number rest of the 2 will be zeros at the start.

If you check Excel's formula bar, you have the actual number value in the cell and you can use this number for calculation which is not possible in rest of the methods.

Sample File


Most of the times…

…you need to insert a zero before a number when we are dealing with some specific type of numbers…

…i.e. invoice numbers, mobile numbers, zip codes, etc.

With normal numbers, this kind of situations never arises because…

…we are more into calculation not about the format.

But if it arises, you have now 5 different methods to deal with it and I hope these methods will help you but now tell me one thing.

Do you have any other method to add leading zeros before a number?

Make sure to share your views with me in the comment section, I'd love to hear from you. And please, don’t forget to share this post with your friends, I am sure they will appreciate it.

You must Read these Next

  1. Remove First Character: Even I have created a small UDF to make it super easy to for him to remove any number...
  2. Delete Extra Spaces: It happens sometimes you send Excel files to others and they made some changes into formulas...
  3. Bullet Points in Excel: In Excel, most of the time we use serial numbers to in data tables but when you are working...
  4. Add Serial Number in Excel: These methods can generate numbers up to a specific number or can add a running column...
  5. Insert Checkmark in Excel: Eventually today morning, I thought maybe there is more than one way to add a check mark in a cell and...
  6. Hide Formula in Excel: Hiding a formula is a simple way to do this so that others can’t able to see which formula...
  7. Convert a Formula to Value: Sometimes it’s important to change a formula into its result value and I’ve found that...

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.


24 thoughts

Leave a Comment

Your email address will not be published.

  1. Hi Puneet,
    Excellent work. Keep it up.
    i found the 3rd option (REPT function) the best. you can customise the length of the number by linking “7” in the example shown to a cell with value of desired length.

    All the Best.

  2. Hi,
    I have this formula for date value =A1-Date(Year(A1),1,0) everything works fine. range A1 contains 01/01/2019. but when I modified the code =”LTL”&A1-(Date(Year(A1),1,0)&”/”&Text(A1,”yy”) for my wish “LTL001/19”. the return value has no leading zeros like “LTL1/19”.
    the text “LTL” and “/” are static. 001 change if days changed, 19 also change if years changed
    what should I do for fix this?
    thanks in advance.

  3. Hi Puneet

    My variation on your number 3 is


    where Fieldlength is a named value (or you can refer to a cell if you prefer).

    The reason I prefer it is that you only have to point to one cell when creating it,

    and can amend the length from a single place even if the formula is scattered all over your workbook.

    Keep up the good ideas, I really appreciate your approach.

    Best wishes


  4. When you load a file where an apostrophe has been used, or the column has been formatted as text, Excel will still convert it to a number and drop off the leading zero(s). I don’t have control over the files I receive.

  5. My issue is loading csv files. The number can be formatted and saved with leading zeros, and even have inverted commas, but Excel converts them back to ordinary numbers when you reload the file.

    • Format the columns where you want to enter csv numbers as text. Highlight the columns, then Ctr+1, then select text and click ok.

      • Thanks Baah.

        However, mine is an automated process where the invoice/account numbers are of differing lengths and may have differing numbers of leading zeros which I have now lost irretrievably.

  6. Hi puneet, you are awesome. You have really made excel as very enjoyable.

    For leading zero – I always use ‘ (apostrophe) before typing in any number. The cell retains whatever you type.

  7. Do you have a way to autonumber in a table? I want to automatically add a unique Order ID every time a new order is entered in a table.