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

Add Leading Zeros

There are few default rules in Excel which can annoy you. And, 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.

So let’s get started.

1. Convert a Number into a Text to Add Leading 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.

Steps:​

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

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

2. Use Concatenate Function to Add Leading Zeros

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

With Concatenate function, you are combining zeros with actual numbers. 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

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

3. Add Dynamic Leading Zeros with 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, we have used REPT Function 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.

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

4. Use TEXT Function to Add Leading Zeros

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.

  • 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.
using custom format to add leading zeros in excel

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 the cell. And, you can use this number for calculation which is not possible in rest of the methods.

Sample File

Download this sample file for here to learn more.

Conclusion

Most of the times we 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 5 different methods to deal with it. And, I hope these methods will help you.

Now tell me one thing. Do you have any other method to add leading zeros? And if you have, please share with me in the comment section, I would love to hear from you.

And, please don’t forget to share this tip with your friends. I'm sure they will appreciate it.

try this next

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