How to Insert 0 Before Any Number on Excel?


While working on an Excel worksheet, you may see that Excel doesn’t allow 0 before any numbers. So, you can’t make a column with the same number of digits. If you just put a 0 before other digits and press Enter, Excel will not show the 0 but the different digits only. This can happen due to problems with phone numbers, IDs, bank account numbers, etc. For instance, write 000012 and hit Enter; Excel will automatically remove the leading zeros.

It can be frustrating if you don’t know how to make Excel keep those leading zeroes. But there are some easy and tricky methods to put 0 in Excel in front of numbers. This risewindows article will show you how to insert 0 before Any Number in Excel.

How to Add 0 Before Any Number in Excel?

You can insert or add 0 before any number in an Excel worksheet in several ways. We will guide you through these procedures.

Format as Text

It is possible to change the format of a range in Excel to text. Excel will treat any numbers you enter as text values if you do this.

Step 1. First, open a new or existing Excel worksheet.

Step 2. Now, select a range of cells in which you want to enter leading zeros.

Step 3. Then, in the menu bar, click on the Home tab to change the ribbon.

Step 4. After that, click on the Number Format drop-down selection in the Number section.

Step 5. Next, select Text in the drop-down list.

Because text values are entered instead of numbers, numbers with leading zeros will not disappear when you enter them.

Format Cells

Using the Format Cells dialog, you can add leading 0 before a number in Excel. This is useful if you have similar types of data, such as several 10-digit phone numbers, or if you want to display several numbers similarly. In such cases, you don’t need to add 0 before a number; Excel will do it for you. The Excel spreadsheet will add the leading 0 to a 9-digit number when you press Enter. Here is how to do it.

Step 1. First, open a new or existing Excel worksheet.

Step 2. Select several blank or filled cells where you want to display numbers similarly.

Step 3. Then, right-click on the cells and select the Format Cells option to open the Format Cells dialog box.

Step 4. After that, switch to the Number tab.

Step 5. Now, select Custom in the left sidebar.

Step 6. Then, enter several 0 you want to see in chosen cells.

Step 7. Lastly, click the OKbutton.

For instance, if you have entered five 0 in the above step, when you type any number, such as 1, in an Excel cell and press Enter, Excel will automatically display 1 as 00001. That means you don’t need to type 00001 to match with other numbers in your cells.

Leading Apostrophe

If you use a leading apostrophe (‘) to force Excel to enter a number as text, you will keep the zeroes in front as you enter your data. This method is quick and easy if you have to enter a few numbers leading to 0. You can do this by typing[ before any numbers. This will make Excel see the data as text and not a number.

That’s it for the article.

I hope this article will be helpful to you.

Good luck & stay healthy, guys.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.