Technology

How to add a leading zero to numbers and text in Excel

This issue occurs because Microsoft Excel treats zip codes, phone numbers, and similar items as numbers and applies a general formatting that removes leading zeros. In this article, meohay.vn will guide you on how to add leading zeros to numbers in Excel.

Administrator

7 January, 2024

115

Method 1: Displaying leading zeros in Excel using custom formatting

To display a leading zero, you can apply custom number formatting by following these steps:

Select one or multiple cells where you want to display leading zeros, then press ctrl + 1 to open the Format Cells dialog box.
In the Category section, select Custom.
Enter the formatting code in the Type section.
In many cases, you’ll need a formatting code with a 0 character, such as 00. The number of 0’s in the formatting code corresponds to the total number of digits you want to display in a cell (you can follow the example below).
Click OK to save the changes.

Example: To add a leading zero to create a 5-digit number, you can use the formatting code 00000

By using Excel’s custom number formats, you can add leading zeros to create numbers with fixed lengths, like the example above, and also numbers with variable lengths. All this can be done by using the formatting codes:

0 – display an additional zero.
# – do not display an additional zero.

For example, if you apply the format #000 to some cells, regardless of the number you enter, it will have 3 leading zeros. Your custom number formats can also include spaces, hyphens, parentheses, etc.

The following spreadsheet shows examples of custom formats that can display leading zeros in Excel:

The following formatting codes can be used to display numbers in special formats such as zip codes, phone numbers, credit card numbers, and social security numbers:

Tip: Excel has some built-in formats for postal codes, phone numbers, and social security numbers, as shown in the image below:

Note: This method is best used when working with a set of numeric data, where the result is numbers rather than text. This method only changes the display of a number but does not change its actual value: although additional zeros are displayed in the cells, the actual value of the original number remains unchanged. When performing calculations on cells containing these numbers, the calculations are performed based on the original values. Custom formats can be applied to numeric (consisting of numbers and dates) data, and the results will also be numbers or dates.

Method 2: Displaying leading zeros in Excel using the TEXT function

Custom number formatting allows you to display a leading zero in front of a number without changing the underlying value of the number, while the Excel TEXT function adds leading zeros to the original number by changing the nature of the number itself.
To add leading zeros to a value using the TEXT function, you use the same formatting codes as in custom number formats. However, the result of the TEXT function is always a text string, even though it may look like a number.

Example: To prepend a 0 before a value in cell A2, you use the formula:
= TEXT (A2, “0 #”)
To create a string with a fixed length of 5 characters with leading zeros, you can use the following string:
= TEXT (A2, “00000”)
Note that the TEXT function requires the formatting code to be enclosed in double quotes, and the result is as follows:

Note: The Excel TEXT function always returns a text string rather than a number, so you cannot use it in numerical calculations and other formulas. It can only be used to compare the output with other text strings.

Adding leading zeros to a text string

In the previous examples, you saw how to add leading zeros to a number in Excel. But what if you want to add leading zeros to a text string, such as 0A102? In that case, both the TEXT function and custom formatting codes will not work because they can only handle numeric values.
If the value you want to add zeros to contains alphabetic characters or text, you can apply one of the following methods, which are general solutions for both numbers and text strings.

Formula 1: Adding leading zeros using the RIGHT function

The simplest way to put a leading zero in front of text strings in Excel is to use the RIGHT function:
RIGHT(“0000” & cell, string_length)
Where:
“0000” is the maximum number of zeros you want to add. For example, to add 2 zeros, you enter “00”.
Cell is a reference to the cell that contains the original value.
String_length is the number of characters the resulting string should contain.
Example: To create a character string with 6 leading zeros based on the value in cell A2, you use the formula:
= RIGHT (“000000” & A2, 6)
This formula works by adding 6 zeros to the value in cell A2 (“000000″&A2), and then extracting the rightmost 6 characters. The result is that it only inserts the necessary number of zeros to reach the total number of characters in the string.

In the above example, the maximum number of zeros is equal to the total length of the character string (6 characters), so all resulting strings have a fixed length of 6 characters (a constant length). If you apply it to an empty cell, the formula will return a string of 6 zeros. Depending on the purpose, you can insert different numbers of zeros and change the total number of characters as desired, for example:

=RIGHT(“00″&A2, 6)

The result is a string with a varying length, which can contain a maximum of 2 leading zeros:

Formula 2: Adding leading zeros using the REPT and LEN functions

Another way to insert a leading zero in front of a text string in Excel is to use a combination of the REPT and LEN functions:

REPT(0, number of zeros-LEN(cell))&cell

Example: To add leading zeros to a value in cell A2 to create a 6-character string, you use the formula:
= REPT(0, 6-LEN(A2))&A2

How this formula works:

LEN(A2) gives the total number of characters in cell A2
REPT(0, 6-LEN(A2)) adds unnecessary zeros. To calculate how many zeros to add, subtract the maximum number of zeros from the length of the string in cell A2. Finally, you get the result:

Note: This formula can insert leading zeros before both text strings and numbers, but the result is always a text string, not a number.

Adding an unspecified number of leading zeros to a value

To add a prefix to all values in a column (which can be numbers or text strings) with a predetermined number of leading zeros, you can use the CONCATENATE function or the ampersand (&) operator:
For example, to prepend a 0 to a number in cell A2, you can use the following formulas:

=CONCATENATE(0,A2)

=0&A2

As shown in the screenshot below, the formulas add just one leading zero to the rows for all cells in a column, regardless of how many characters they originally contained.

Similarly, you can insert 2 leading zeros (00), 3 leading zeros (000), or more leading zeros before numbers or text strings.

Frequently asked questions

You can use the TEXT function to format numbers with leading zeros. For example, to add a leading zero to the number 5, use the formula =TEXT(5,”00″). This will return “05”.

If you have text values that you want to pad with leading zeros, you can use the CONCATENATE function along with the REPT function. For instance, if you want to add a leading zero to the text “5”, use the formula =CONCATENATE(REPT(“0”,2-LEN(5)),5). This will return “05”.

Yes, another method is to use custom number formatting. Select the cells you want to format, right-click and choose “Format Cells”, then select “Custom” and enter the format code “00” in the “Type” field. This will ensure that all numbers in the selected cells are displayed with leading zeros.

Yes, you can use Excel’s data validation feature. Select the cells you want to apply the validation to, go to the Data Validation settings, and in the “Input Message” tab, specify the input message and the format you want, such as “00”. In the “Data” tab, select “Text Length” under “Data”, and set the minimum and maximum text length to 2. This ensures that any new data entered must be in the format with leading zeros.