Mastering Number Formatting: How To Precisely Add Prefixes Like Zeroes in Excel Cells

Quick Links

• Keep the Leading Zero as you Type
• Apply Formatting to Keep the Zero
• Using Custom Formatting to Keep it Numeric

When entering numbers into Excel, zeros at the beginning of the number are removed. This can be an issue when entering phone numbers and IDs. In this article, we will cover ways to solve this problem and keep the leading zeros.

Keep the Leading Zero as you Type

If you wanted to ensure that the leading zero is kept when typing, enter a single quote before you type 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 shown in the top 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. Select “Ignore Error’ to proceed and store the number as text.

The green triangle should then disappear.

Apply Formatting to Keep the Zero

It is quick and simple to apply text formatting as you type, but it is even more efficient to do it in advance.

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

The values you enter into this formatted range will now automatically be stored as text, and leading zeros preserved.

Using Custom Formatting to Keep it Numeric

The previous two options are great and sufficient for most needs. But what if you needed it as a number because you are to perform some calculations on it?

For example, maybe you have an ID number for invoices you have in a list. These ID numbers are exactly five characters in length for consistency such as 00055 and 03116.

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

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

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

Entering the five zeros forces a fixed-length number format. If just three numbers are entered into the cell, two extra zeros are automatically added to the beginning of the number.

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

Excel’s ability to format cells is an excellent tool to provide consistent formatting of phone numbers, credit card numbers, and IDs—especially when the data is entered by multiple people.

Also read: