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

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

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.

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

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.

**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”)

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

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.

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.

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.

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.

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

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.

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.

- R1C1 Reference Style In Excel
- Select Non-Adjacent Cells
- Different Formatting In a Single Cell
- 3D Reference In Excel

- Lisa D
- Puneet Gogia
- Lisa D
- Puneet Gogia
- Lisa D
- Puneet Gogia
- Puneet Gogia

- Ogundepo Ezekiel Adebayo
- Puneet Gogia

- Satish Karanth
- Puneet Gogia