Email Us: ahsam_easy[at]yahoo.com

For Advertise, Call or SMS: +88 01911095352

How To Add Zero “0” In Front Of Numbers In MS Excel

You are here because you are looking for how to add zero in front of number in excel sheet. Well, as we all know by default excel don’t show any zero digits in front of numbers in excel cell.For example: If you are going to make a list of zip codes or phone numbers that contains a zero digit in front of the codes or numbers then you can’t put it directly instead of doing some tricks.

A couple of days ago I was going to make a list of phone numbers that contains a zero digit in front of all numbers and when I started writing, the zero digits did not appear. I found its solution and I thought this trick will be helpful for my beloved readers who worked in MS excel.

How To Insert Zero In Excel Using Format Cells

1. First select the cells and rows of mobile numbers. Alternatively, if your sheet contains only mobile numbers or zip codes or something like that then just press CTRL+A to select all.

2. Press CTRL+1 on the keyboard or right click your mouse button and select Format Cells.

3. Go to Number tab and select Custom.

4. In the Type box enter zero digits according to your actual zip or phone numbers.

For example: If your actual mobile number is: 01911095352 then normally it will show without zero in excel sheet as 1911095352. Now fact is, here actual mobile number contains 11 digits and you must enter 11 zeros in Format Cells Type box.

5. Finally, click OK button and you will see a zero digit are showing in front of all mobile numbers or after writing, the zero digit are not disappearing.

How To Add A Zero Digit In Excel Using A Formula

This method is somewhat tricky but don’t worry I will make it easy for you.

1. First write all mobile numbers one by one without first zero digit as showing below.

2. Now point your cursor to cell B1 and write the formula.

=TEXT(A1,"00000000000")

Make sure you enter actual digits of zeros according to your actual mobile number.For example: If your original mobile number is 11 digits with zero then enter 11 zeros in the formula.3. Finally press Enter button from keyboard.

4. Now, copy the formula from cell B1 by pressing CTRL+C and select the range of all mobile number fields by pressing SHIFT+ Arrow Key and press CTRL+V to paste.

Finally you are all done. You will see the zero digit in front of all mobile numbers.