How to add zeros before the number in Excel

Generally, in Excel, the user can't be able to add a zero before the numbers. If the value zero is typed before the respective numbers, it returns the number without the value zero. Sometimes the user needs to add a zero before the value based on the data required, such as phone numbers, invoice numbers, zip codes, etc. Excel provides a solution to this problem by using various functions and formulas. Here in this tutorial, the various steps to add zero before the number are explained briefly.

Example 1: How to add zero before the specified number?

Here in this example, add a zero before the value. First, the number needs to be converted into text and the value zero is added. The steps to be followed are,

Step 1: Select a cell namely A1, where the user needs to enter the value.

Step 2: Go to the Home tab. In that click the 'Number' option present in the tab. A drop-down list will appear, in that choose Text format.

How to add zeros before the number in Excel

Step 3: Click OK. Now enter the value zero in cell A1 and the respective numbers after it.

Step 4: Excel returns the number before zero in it.

In the worksheet, the value is preceded by a zero value. As it is in text format, further it can't be used for any calculation.

Example 2: Add the value zero before the data using the concatenation function

In this example, the concatenation formula is used to combine the value zero with the other number. The steps to be followed are,

Step 1: Select a new cell, namely A1, where the user needs to display the result and enter the formula as =CONCATENATE ("0", "5678")

Step 2: Press Enter. The result will be displayed in cell A1 with zero value before the respective numbers.

As the numbers are in text format, they can't be further used in calculating any types.

Example 3: Add the value zero before the data using the REPT function

As the name suggests, REPT repeats the text several times. The REPT function adds the value zero before the specified number. The steps to be followed are,

Step 1: Select a cell namely A1 and enter the respective data. Here the data entered is 7.

Step 2: To add one zero before the value, select a new cell, B1, where the user wants to enter the data.

Step 3: Select B1 and enter the formula as =REPT (0, 2-LEN (A1))) &A1. The formula A1 indicates the cell containing data.

Step 4: Press Enter. The result will be displayed in cell B1, which is preceded by the value zero.

In the formula, the value 2 is used as cell A1 contains only one data called 7. If it contains two data, the value 3 is added in the formula, which displays the value zero before the two data. As the numbers are in text format, they can't be further used in calculating any types.

Similarly, the method follows for the other data to display the zero before the respective number.

Example 4: Add the value zero before the range of data using the REPT function

In example 3, the REPT function is used for single data. In this example, the REPT function is used to add the value zero before the range of data. The steps to be followed are,

Step 1: Select a cell, A1:A6, and enter the respective data. Here the data entered is 5 to 567891.

Step 2: To add zeros before the value, select a new cell, B1, where the user wants to enter the data.

Step 3: Select B1 and enter the formula as =REPT (0, 7-LEN (A1))) &A1. The formula A1 indicates the cell containing data.

Step 4: Press Enter. The result will be displayed in cell B1, which is preceded by the value zero.

Here in the worksheet, the formula returns the value zero before the data based on the formula. In the formula, the value 7 is used, hence in the data range from B1:B6, the value of zero decreases from 6 to 1. Hence this formula is called dynamic formula. As the numbers are in text format, they can't be further used in calculating any types.

Similarly, this method is followed for the other data range.

Example 5: Add the value zero before the range of data using the TEXT function

In the previous examples, the data is converted into a text function, and zero is added. In this example, the TEXT formula adds a specified zero before the data.

The syntax for the Text function

=TEXT (value, format text)

The steps to be followed are,

Step 1: Select a cell, A1:A6, and enter the respective data. Here the data entered is 5 to 567891.

Step 2: To add zeros before the value, select a new cell namely B1 where the user wants to display the result

Step 3: Select B1 and enter the formula as =TEXT (A1, "0000000"). The formula A1 indicates the cell containing data.

Step 4: Press Enter. The result will be displayed in cell B1, which is preceded by the value zero.

Here in the worksheet, the formula returns the value zero before the data based on the formula. In the formula, seven zeros are used. Hence in the data range from B1:B6, the value of zero decreases from 6 to 1. Hence this formula is called dynamic formula. As the numbers are in text format, they can't be further used in calculating any types.

Similarly, this method is followed for the other data range.

Example 6: Add the value zero before the data using the TEXT function

In Example 5 the TEXT function is used for a range of data. In this example, it is used for Specified data. The steps to be followed are,

Step 1: Select a cell namely A1 and enter the respective data. Here the data entered is 5.

Step 2: To add one zero before the value, select a new cell namely B1 where the user wants to display the result.

Step 3: Select B1 and enter the formula as =TEXT (A1, "00"). The formula A1 indicates the cell containing data.

Step 4: Press Enter. The result will be displayed in cell B1, which is preceded by the value zero.

In the formula, two zeros are used as cell A1 contains only one data called 5. If it contains two data, then three zeros are added in the formula, which displays the value zero before the two data. As the numbers are in text format, they can't be further used in calculating any types.

Similarly, the method follows for the other data to display the zero before the respective number.

Example 7: Add the value zero before the data using a Custom format.

In this example, the value zero is added to the prefix of the data using a custom format.

Step 1: Select a cell, A1, where the user wants to display the result.

Step 2: Right-click towards the cell, click the format cell option, or press CTRL+1. In the Format Cell dialog box, click the Custom in Number tab option.

Step 3: In the Type box, enter the specified number of zeros, and Press OK.

Step 4: The value will be displayed in cell A1

based on the number of zeros.

Six zeros are placed in the format cell, and five zeros are placed before the number 1. In the Excel formula bar, the actual value is present. Hence it should not be used in other calculations.

Example 7.1: Add the value zero before the range of data using the Custom format.

In example 7, the value zero is added in the prefix of the data using a custom format. In this example, the value zero is added before the range of data.

Step 1: Select a cell namely A1, where the user wants to display the result.

Step 2: Right-click towards the cell, click the format cell option, or press CTRL+1. In the Format Cell dialog box, click the Custom in Number tab option.

Step 3: In the Type box, enter the specified number of zeros, and Press OK.

Step 4: Based on the number of zeros the value will be displayed in cell A1.

As six zeros are placed in the format cell, five zeros are placed before the number 1, and it reduces to 1 by simply dragging the formula. In the Excel formula bar, the actual value is present. Hence it should not be used in the other calculations.