Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

How To Add Leading Zeros In Excel

how to add leading zeros in an excel cell

You can’t add a zero before a number in excel.

Whenever you try to add a leading zero, it disappears.

Not matter what, how many zeros you trying to add, it will disappear.

Let me tell you what’s the reason for this.

In below example, I am trying to start a number with 0 but I am unable to do that.

Why You Are Not Able To Add Leading Zeros In Excel

Now, if you check, the number format is “General”.

When you add any number in excel, by default excel will convert that number into General or Number format. And, both of the number formats ignore prefix zeros.

So today in this post, you will learn that how to add a leading zero in excel using different methods.

Now let’s discuss that how can we stop excel from deleting 0 by using different methods. Here I have listed some methods to keep leading zeros in the cell.

  1. Text Format
  2. Concatenate Function
  3. REPT Function
  4. Text Function
  5. Custom Format

1. Text Format

This is the easiest method to add leading zeros in numbers.

  • Select the cells in which you want to add the leading zeros.
  • Go to Home → Number → Change 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 your numbers.

Important Note: You can’t use these number in any type of calculation because they are now in text format.

2. Concatenate Function

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

  • You can use a formula like below to add a zero before numbers in a cell.
  • =CONCATENATE(“0″,”123458”)

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

Important Note: You can’t use these number in any type of calculation because they are now in text format.

3.REPT Function

REPT Function is one my favorite functions in excel which can repeat a text a number of times. Now, let me show how it can help us to prefix 0 in excel.

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

In above example, I am using REPT Function to get total 8 digits in a cell. If a cell has 6 numbers then rest 2 digits will be zero & 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.

Important Note: You can’t use these number in any type of calculation because they are now in text format.

4. Text Function

You can also use Text Function to add leading zeros in a cell. Text Function will give you the same result as you get by using text format.

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

In above example, I have converted a number into a text in which you will always get 7 digits. If you have 5 digits number rest of the 2 will zeros at the start of the number.

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.

Important Note: You can’t use these number in any type of calculation because they are in text format.

5. Custom Format

This method has some advantages over the other methods.

Just follow these simple steps.

  • 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.

Add-Leading-Zeros-In-Excel-7

In above example, I 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 formula bar, you have the actual number value in your cell. And, you can use this number for calculation which is not possible in rest of the methods.

Sample File

download sample file to learn about add leading zeros in excel

Conclusion

Now you know to 5 different ways to add leading zeros in excel. And, you can use any of the methods which you think is the best for you.

I prefer to use the custom format, it allows me to use numbers further in calculations.

What do you think which method is best to use? Do you know about all these methods before? or you are using any other method?

Please share with me in the comment box.

More Tips



  • Lisa D

    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.

    • Puneet Gogia

      Hey Lisa,

      How you are entering data in your table. I mean manually or by some kind of userform.

      • Lisa D

        Manually.

        • Puneet Gogia

          Please Check This File.

          I hope this will help

          http://goo.gl/aBd985

          • Lisa D

            Excellent, thank you! I find your blog very useful!

          • Puneet Gogia

            Hey Lisa,

            Thanks for your words.

          • Puneet Gogia

            Hey Lisa,

            Thanks for your words.

  • Ogundepo Ezekiel Adebayo

    Method 5 is the best

    • Puneet Gogia

      Yup.

  • Satish Karanth

    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.

    • Puneet Gogia

      I’m so glad you liked it.