How to Add Leading Zeros in Excel

There are a few default rules in Excel that can annoy you. One of those rules is you can’t add a zero before a number.

The reason behind this is that a zero before a number makes no sense and has 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 numbers, zip codes, 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.

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.

• Select the cells in which you want to add the leading zeros.
• Go to Home ➜ Number ➜ Change the number format to text.

Now you can easily add zeros at the start of the numbers. You can’t use these numbers further in any type of calculation because they are now in text format.

Use Concatenate Function to Add a Zero

If you want to insert 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 the one below to add a zero before numbers in a cell.

=CONCATENATE("0","123458")

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

Add Dynamic Leading Zeros with REPT Function

REPT is one of my favorite Excel functions which can repeat a text a number of times. Now let me show you how it can help us to prefix zero in a cell.

=REPT(0,7-LEN(B5))&B5

In the above example, we have used REPT to get a total of 8 digits in a cell. If a cell has 6 numbers, then the rest of the 2 digits will be zero and if a cell has 4 numbers, then the rest of the 4 numbers will be zero.

This is a dynamic formula to get zero before the numbers. You can’t use these numbers further in any type of calculation because they are now in text format.

Use TEXT Function to Add Leading Zeros Format

You can also use the TEXT function to add leading zeros. TEXT Function will give you the same result as you can get by using text format.

=TEXT(B6,"0000000")

In the 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 the text function, you just have to refer to the cell which has the value and then the format as I have specified in the above example. You can’t use these numbers in any type of calculation because they are now in the text format.

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.

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

In the above example, we have formatted the cells with 7-digit numbers 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 the rest of the methods.

Conclusion

Most of the time 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 situation 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.