How to Add Zero In Front of Number in Excel (7 Easy Ways)

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. 

Dataset where Excel removed zero before product codes

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.
Select the range
  1. 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.

Right-click and select format cells

Or

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

Click in the Number dialog box launcher
  1. In the Format Cells dialog box, in the Number tab, select Text in the Category list box and click OK.
Select text

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.

Seelct text from the drop down
  1. Re-enter each product code in column A into column B, beginning each code with a zero.
Renenter names  with leading zeros

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.

Error when numbers are stored as text

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

Ignore error

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.

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. 

Dataset where Excel removed zero before product codes

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:
Select the end and enter apostrophe and then the number
  1. Press the Enter key or click the Enter button on the formula bar.
  2. Enter the other product codes in the same way as the first one.
Numbers are shown with leading zeros
  1. To remove the error indicators, select range B2:B5, click the error indicator icon, and choose Ignore Error from the menu that appears. 
Ignore error

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

Numbers are shown with zero in Front of Number
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. 

Dataset where Excel removed zero before product codes

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.
Select the range
  1. Press Ctrl + 1 to launch the Format Cells dialog box.
  2. In the Format Cells dialog box, in the Number tab, select Custom in the Category list box.
Select custom format
  1. 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:
Entre “0”#

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.

  1. Enter the product codes in column A into column B starting each code with a zero.
Numbers with one leading 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.

Dataset where Excel removed zero before 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)
CONCAT formula
  1. Press the Enter key or click the Enter button on the formula bar.
  2. Double-click or drag down the fill handle to copy the formula down the column.
Apply formula to the entire 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. 

Dataset where Excel removed zero before product codes

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:
Add hyphen between 0 and the number
  1. Enter the rest of the product codes in a similar fashion.
Zero added in front of number but with a hyphen

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. 

Dataset where Excel removed zero before product codes

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

Ampersand formula
  1. Press Enter on the keyboard or click the Enter button on the formula bar.
  2. Double-click or drag down the fill handle to copy the formula down the column.
Zero added in front of the number

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. 

Dataset where Excel removed zero before product codes

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″)

TEXT formula
  1. Press Enter on the keyboard or click the Enter button on the formula bar.
  2. Double-click or drag down the fill handle to copy the formula down the column.
Zero added in front of the number

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: