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.
Now you can easily add zeros at the starting of your numbers.
If you have a fixed numbers of zeros to add before a number, you can use concatenate function.
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.
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.
This method has some advantages over the other methods.
Just follow these simple steps.
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.