How to Add Leading Zeros to Numbers and Text in Excel

Method 1: Displaying Leading Zeros in Excel Using Custom Formatting

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

1. Select one or more cells where you need to display leading zeros and press Ctrl + 1 to open the Format Cells dialog box.
2. In the Category section, choose Custom.
3. Enter the format code in the Type field.
   In many cases, you’ll need a format code with the character “0,” such as “00.” The number of zeros in the format code corresponds to the total number of digits you want to display in a cell (refer to the examples below).
4. Click OK to save the changes.

Example: To add leading zeros to create a 5-digit number, use the format code “00000.”

Using Excel’s custom number formatting, you can add leading zeros to create numbers of fixed or variable length, as shown in the example above. This is achieved by using format codes:
0 – displays additional zeros
# – does not display additional zeros
For instance, applying the format “#000” to a cell will add three leading zeros to any number entered.
Custom number formats can also include spaces, hyphens, parentheses, and more.
The following table provides examples of custom formats that display leading zeros in Excel.

The following format codes can be used to display numbers in specific formats, such as zip codes, phone numbers, credit card numbers, and social security numbers.

Tip: Excel has built-in formats for zip codes, phone numbers, and social security numbers, as shown below:

Note: This method is best used when working with numerical data where the result is a number, not text. Custom formatting only changes the display of a number without altering its underlying value. Calculations will still use the original value. Custom formats can be applied to numerical data (numbers and dates) and will yield numerical or date results.

Method 2: Displaying Leading Zeros in Excel Using the TEXT Function

While custom number formatting displays leading zeros without changing the underlying value, the Excel TEXT function adds leading zeros, altering the number’s nature.
To add leading zeros using the TEXT(value, format_text) function, use format codes similar to those in custom number formatting. However, the TEXT function always returns text, even if it appears as a number.
Example: To add leading zeros to a value in cell A2, use the formula:
=TEXT(A2, “0 #”)
For a fixed-length string of 5 characters with leading zeros, use:
=TEXT(A2, “00000”)
Remember to enclose the format code in double quotes. The result is:

Note: The TEXT function always returns text, not a number, so it cannot be used in arithmetic calculations or formulas, only for comparing output with other text strings.

ADDING LEADING ZEROS TO TEXT STRINGS

In previous examples, we added leading zeros to numbers in Excel. But what if you want to add leading zeros to a text string, like “0A102”? In such cases, neither the TEXT function nor custom formatting will work, as they only handle numerical values.
If the value contains letters or text, use one of the following methods, which work for both numbers and text strings.

Formula 1: Adding Leading Zeros Using the RIGHT Function

The simplest way to add leading zeros to text strings in Excel is by using the RIGHT function:
RIGHT(“0000” & cell, string_length)
Where:
“0000” is the maximum number of zeros to add. For example, use “00” for two zeros.
Cell references the cell containing the initial value.
String_length is the desired length of the result string.
Example: To create a 6-character string with leading zeros based on the value in cell A2, use:
=RIGHT(“000000” & A2, 6)
This formula adds six zeros to the value in A2 (“000000″&A2) and then extracts the last 6 characters, ensuring the correct number of leading zeros.

In this example, the maximum number of zeros equals the total string length (6 characters), resulting in fixed-length strings. For an empty cell, the formula returns six zeros.
Depending on your needs, adjust the number of zeros and total characters. For example:

=RIGHT(“00″&A2, 6)

This formula produces variable-length strings with up to two leading zeros:

Formula 2: Adding Leading Zeros Using REPT and LEN Functions

Another way to add leading zeros to text strings in Excel is by combining the REPT and LEN functions:

REPT(“0”, number of zeros – LEN(cell)) & cell

Example: To add leading zeros to a value in cell A2, creating a 6-character string, use:
=REPT(“0”, 6 – LEN(A2)) & A2

How This Formula Works:

REPT repeats a character a specified number of times, and LEN returns the length of a string. The logic is as follows:

LEN(A2) returns the number of characters in cell A2.
REPT(“0”, 6 – LEN(A2)) adds the necessary zeros by subtracting the length of A2 from the maximum number of zeros. The result is:

Note: This formula adds leading zeros to text strings or numbers, but the result is always text, not a number.

ADDING A VARIABLE NUMBER OF LEADING ZEROS

To add a variable number of leading zeros to all values in a column (numbers or text), use the CONCATENATE function or the ampersand operator.
For example, to add a single leading zero to a number in cell A2, use:

=CONCATENATE(“0”, A2)

or

= “0” & A2

As shown below, this formula adds one leading zero to all cells in a column, regardless of their initial length.

Similarly, you can add two zeros (“00”), three zeros (“000”), or more before numbers or text strings.

Note: The result of this formula is always text, even if the initial value is a number.

Life Hacks

Expert in web design and development with over 15 years of experience. I’ve helped hundreds of businesses develop comprehensive website plans for sustainable growth and profitability.