How to enter zero before a number in Excel

When entering numbers in Excel, zeros are removed at the beginning of the number. This can be a hindrance when entering phone numbers and IDs. In this post, we will cover alternatives to solve this problem and keep leading zeros.

Keep leading zero while typing

If you want to make sure the leading zero is kept when typing, enter a single quote before typing the number.

This instructs Excel to store the value as text and not as a number.

When you press “Enter” to confirm, a green triangle is displayed in the upper left corner of the cell. Excel is checking that you intended to do that or if you want to convert it to a number.

Click the diamond icon to display a list of actions. Please select “Ignore error” to continue and store the number as text.

The green triangle should disappear.

Format to keep zero

It's quick and easy to format text as you type, but it is even more effective to do it in advance.

Select the range of cells you want to format as text. Next, click on the tab “Beginning”, select the list arrow in the Number group and choose “Text”.

Values ​​you enter in this formatted range will now be automatically stored as text and leading zeros will be preserved.

Use custom format to keep it numeric

The above two options are excellent and sufficient for most needs.. But, What if you need it as a number because you have to do some calculations on it?

As an example, maybe you have an identification number for the invoices you have listed. These ID numbers are exactly five characters long for consistency, What 00055 and 03116.

To perform basic calculations such as adding or subtracting one to increase the invoice number automatically, you would need to store it as a number to perform such a calculation.

Select the range of cells you want to format. Right-click the selected range and click “Cell format”.

From the "Number" tab, select "Custom" in the Category list and enter 00000 in the Type field.

When entering the five zeros, a fixed length number format is forced. If only three numbers are entered in the cell, two extra zeros are automatically added to the beginning of the number.

You can play around with the custom number format to get the exact format you need.

---

Excel's ability to format cells is an excellent tool for providing consistent formatting of phone numbers, credit card and ID numbers, especially when data is entered by multiple people.