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.

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 youll 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, Id like to share with you 5 different methods which we can use to add leading zeros in Excel.

Top 5 Ways to Add Leading Zeros in Excel Convert a Number into a Text Use Concatenate Function Using REPT Function Create a Formula with TEXT Function Apply Custom Format.without any delay lets get started.

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.

Alert: You cant use these numbers further in any type of calculation because they are now in text format.

Use Concatenate Function to combine a Zero 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) Alert: You cant 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 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 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.

Use TEXT Function to Add Leading Zeros Format 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.

TEXT (B6,0000000) 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 cant use these numbers in any type of calculation because they are now in the text format.

I believe this is the best method to use..and it has an additional benefit over all the other methods.

Right click Format Option or you can also use the shortcut key Ctrl 1 open Format Cell Dialog Box.

In custom input dialog box, enter 0000000.

Click OK. In the above example, we 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.

Excel For Mac Add Padding In Cell