Padding numbers with zeros means adding extra zeros to the left of a number to make it have a certain number of digits. For example, if we want to pad the number 17 with five zeros, the result is 00017. This can be useful for formatting codes, IDs, serial numbers, or other numerical data that require a fixed length.
There are different ways to pad numbers with zeros in Excel formula, depending on whether we want to convert the number to text or keep it as a number. In this article, we will explore three methods:
- Using the TEXT function
- Using the TEXT and REPT functions
- Using the custom number format
Method 1: Using the TEXT Function
The TEXT function converts a number to text using a specified format. The syntax of the TEXT function is:
=TEXT(value, format_text)
where value is the number we want to convert, and format_text is the text string that defines the number format. For example, to pad the number 17 with five zeros, we can use the formula:
=TEXT(17, "00000")
The result is “00017” as a text value. Note that the number of zeros in the format_text argument determines the number of digits in the result. If the number of digits in the value is less than the number of zeros, Excel will add extra zeros to the left. If the number of digits in the value is equal to or greater than the number of zeros, Excel will display the value as it is.
Method 2: Using the TEXT and REPT Functions
Another way to pad numbers with zeros is to combine the TEXT and REPT functions. The REPT function repeats a text string a given number of times. The syntax of the REPT function is:
=REPT(text, number_times)
where text is the text we want to repeat, and number_times is the number of times to repeat it. For example, to repeat the character “0” five times, we can use the formula:
=REPT("0", 5)
The result is “00000” as a text value. We can use this formula as the format_text argument for the TEXT function to pad numbers with zeros. For example, to pad the number 17 with five zeros, we can use the formula:
=TEXT(17, REPT("0", 5))
The result is “00017” as a text value. This method is useful when we want to pad numbers with a variable number of zeros based on another cell or a formula. For example, if we have the number of zeros in cell B2, we can use the formula:
=TEXT(17, REPT("0", B2))
The result will change depending on the value in cell B2.
Method 3: Using the Custom Number Format
The first two methods we have learned both convert the number to text, which may not suit our needs if we want to keep the number as a number. To simply display a number with zeros without changing its value, we can use the custom number format. To do this, follow these steps:
- Select the cell or range of cells that contain the numbers we want to pad
- Press Ctrl + 1 to open the Format Cells dialog box
- In the Number tab, select Custom from the Category list
- In the Type box, enter the number of zeros we want to pad, such as “00000”
- Click OK
The result is that the numbers are displayed with zeros, but they remain as numbers. For example, if we apply the custom number format “00000” to the number 17, it will show as “00017” in the cell, but the value in the formula bar will still be 17.
