Latest Updates:

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.

How to zero digit in front of a number in excel

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.

SEE ALSO: Easy Way To Break The BIOS Password

Well there are two easy methods I will share to display a zero value in front of numbers in excel sheet. Let’s go forward.

Quick Jump To Method 01 Or Method 02

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.

How to zero digit in front of a number in excel

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.

READ ALSO: How To Create Shutdown Shortcut To Turn Off PC At A Certain Time?

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.

How to zero digit in front of a number in excel

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


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.

How to zero digit in front of a number in excel

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.

Isn’t it cool tricks? Kindly share your comments below… If you need any help regarding this topic please don’t be late to ask me. Well, I am all yours dear. Stay well and have fun. Allah Hafez.
  • Blogger Comments
  • Facebook Comments


Post a Comment

Hi, Your are most welcome and feel free to share your comment(s). We will try to response your comment(s) as soon as possible.

Please DON'T spam here. Spam comments will be deleted just after our review.

Thank You For Being With Us.

Copyright © 2021 Shine Mat | All Right Reserved
Design and Customize By: Ashraful Haque Saimoom and PBThemez | About Us | Contact Us
Scroll to Top