How to Add Zero In Front of Number in Excel?

If you try to add a leading zero in front of a number in Excel, Excel removes it immediately after you press Enter.

This is because a zero before a real number is insignificant.

Nevertheless, there are times when you may want to add a zero in front of a number, for example in the case of a phone number, a product code, a credit card number, and so on.

While this may not be a default setting, there are many different ways you can use to stop Excel from removing the leading zero.

This tutorial shows 7 techniques for adding a zero in front of a number in Excel.

Method #1: Apply the Text Format to the Cells Before Entering Data

This method involves applying the text format to the target range.

Excel treats the data entered in cells that have a text format as text strings even when they are numbers. The values are displayed exactly as entered. So if you enter a number that has zeros in front of the numbers, the zeros will not be removed.

Suppose we have the following data set of product codes. As we entered the product codes in column A, Excel by default removed the leading zero from each code. 

We want to re-enter each product code in column B with a leading zero. 

We use the following steps:

1. Select range B2:B5.

2. Open the Format Cells dialog box using any of the following ways:

Press Ctrl + 1

Or 

Right-click the selected range and click the Format Cells menu item on the shortcut menu.

Or

Select the Home tab, and click the Number Format dialog box launcher button in the bottom right corner of the Number group.

3. In the Format Cells dialog box, in the Number tab, select Text in the Category list box and click OK.

Note: You can also apply the text format to the selected range by selecting the Home tab, and choosing Text in the Number Format drop-down list in the Number group.

4. Re-enter each product code in column A into column B, beginning each code with a zero.

The values in column B are displayed exactly as entered. The values are left-aligned meaning they are text values. 

Note: If you select any of the cells in column A, you see an error indicator displayed next to it.

To remove the error indicator, select the range B2:B5, click the error indicator icon and choose Ignore Error on the menu that appears:

The values in column B are displayed with leading zeroes and without the error indicators.

Note: You can’t use this method with numbers that have already been entered. This method is best suited when you are about to do data entry and you want to not have Excel remove the zeros before the number.

Also read: How to Add Leading Zeros in Excel?

Method #2: Input an Apostrophe Before the Number

This technique involves entering an apostrophe before the number with a leading zero.

Adding an apostrophe when you start typing would ensure that anything you enter in the cell would be considered text. And the best part about this is that the value is displayed exactly as entered but without the apostrophe. 

Suppose we have the following data set of product codes. As we entered the product codes in column A, Excel by default removed the leading zero from each code. 

We want to re-enter each product code in column A into column B with a leading zero. 

We use the steps below:

1. Select cell B2 and type in the product code in cell A2 starting with an apostrophe and a leading zero: ‘07458:

2. Press the Enter key or click the Enter button on the formula bar.
3. Enter the other product codes in the same way as the first one.

4. To remove the error indicators, select range B2:B5, click the error indicator icon, and choose Ignore Error from the menu that appears. 

The values in column B are displayed with leading zeroes and without the error indicators.

Also read: How to Remove Apostrophe in Excel (3 Easy Ways)

Method #3: Use a Custom Number Format

This technique involves building a custom number format that defines the display of a number with a leading zero. 

Suppose we have the following data set of product codes. As we entered the product codes in column A, Excel by default removed the leading zero from each code. 

We want to re-enter each product code in column A into column B with a leading zero. 

We use the following steps:

1. Select range B2:B5.

2. Press Ctrl + 1 to launch the Format Cells dialog box.
3. In the Format Cells dialog box, in the Number tab, select Custom in the Category list box.

4. Delete whatever format is in the Type box on the right of the Category list box and type in the custom number format “0”#,  then click OK:

Note: The zero in quotation marks ensures that Excel keeps the leading zero. The hash mark (#) defines any number of digits you may want to follow the leading zero.

5. Enter the product codes in column A into column B starting each code with a zero.

Excel does not remove the leading zeroes from the product codes in column B. The product codes are right-aligned meaning they are numbers and not text strings.

Note: In this method, we have specified that any number that is entered should have a leading zero. This method is not suitable when you have a variable number of zeros before the numbers.

Method #4: Use the CONCAT Function

This method engages the CONCAT function which concatenates or joins a range or a list of text strings.

Suppose we have the following data set of product codes.

As we entered the product codes in column A, Excel by default removed the leading zero from each code. 

We want to re-enter each product code in column A into column B with a leading zero. 

We use the following steps:

1. Select cell B2 and type in the following formula:

=CONCAT("0",A2)

2. Press the Enter key or click the Enter button on the formula bar.
3. Double-click or drag down the fill handle to copy the formula down the column.

The product codes are displayed in column B with leading zeroes. 

Method #5: Input a Hyphen Between the Leading Zero and The Other Digits

In this technique, we input a leading zero and then a hyphen before the other digits of a product code. This turns the product code into a text string that is displayed exactly as entered.

Suppose we have the following data set of product codes. As we entered the product codes in column A, Excel by default removed the leading zero from each code. 

We want to re-enter each product code in column A into column B with a leading zero. 

We use the steps below:

1. Enter the product code in cell A2 into cell B2 beginning with a zero and a hyphen:

2. Enter the rest of the product codes in a similar fashion.

Method #6: Use the Ampersand (&) Operator

In this method, we use the ampersand operator (&) which is Excel’s concatenation operator. 

Suppose we have the following data set of product codes. As we entered the product codes in column A, Excel by default removed the leading zero from each code. 

We want to re-enter each product code in column A into column B with a leading zero. 

We use the following steps:

1. Select cell B2 and type in the following formula:

=”0″&A2

2. Press Enter on the keyboard or click the Enter button on the formula bar.
3. Double-click or drag down the fill handle to copy the formula down the column.

The product codes are displayed in column B with leading zeroes. They are left-aligned because they are text strings. They cannot be used in calculations.

Method #7: Use the TEXT Function

This method engages the TEXT function which converts a value to a text string in a specified number format.

Suppose we have the following data set of product codes. As we entered the product codes in column A, Excel by default removed the leading zero from each code. 

We want to re-enter each product code in column A into column B with a leading zero. 

We use the steps below:

1. Select cell B2 and type in the following formula:

=TEXT(A2,”00000″)

2. Press Enter on the keyboard or click the Enter button on the formula bar.
3. Double-click or drag down the fill handle to copy the formula down the column.

The values in column B are displayed with leading zeroes. They are left-aligned meaning they are text values.

In this tutorial, we looked at 7 techniques for adding a zero in front of a number in Excel. You can use the technique that you are most comfortable with.

Other articles you may also like:

• How to Remove Leading Zeros in Excel (3 Easy Ways)
• How to Remove Space before Text in Excel (5 Easy Tricks)
• How to Get Rid of Scientific Notation in Excel (7 Simple Methods)
• How to Extract Text After Space Character in Excel?
• How to Remove Text after a Specific Character in Excel (3 Easy Methods)
• Show Negative Numbers in Parentheses/Brackets in Excel (2 Easy Methods)