How to Insert 0 Before Any Number in Excel?

Posted by

Excel automatically removes zeros from the beginning of numbers when you enter them. This can cause problems with phone numbers, IDs, bank account numbers, etc. For example, write 000012 and hit Enter; Excel will automatically remove the leading zeros.

Insert Blank Row After Every Data R...
Insert Blank Row After Every Data Row In Excel- Excel Tip

If you don’t know how to make Excel keep those leading zeroes, it can be frustrating. Don’t worry, there are a few ways through which you can accomplish your goal in Excel.

The goal of this gearupwindows article is to help you add leading 0 to a number in Excel. Here is how to do it.

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. If you do this, Excel will treat any numbers you enter as text values.

Step 1. Open a new or existing Excel worksheet.

Step 2. Select a range of cells you want to enter leading zeros in.

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

Step 4. Click on the Number Format drop-down selection in the Number section.

Step 5. 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 also 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 in a similar manner. 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. Open a new or existing Excel worksheet.

Step 2. Select several blank or filled cells where you want to display numbers in a similar manner.

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

Step 4. Switch to the Number tab.

Step 5. Select Custom in the left sidebar.

Step 6. On the “Type” box, enter several 0 you want to see in chosen cells.

Step 7. Finally, click the OK button.

As an example, 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 with leading 0. You can do this by typing  before any numbers. This will make Excel see the data as text and not a number.

Leave a Reply

Your email address will not be published. Required fields are marked *