While working with Excel, we are able to pad a number with zeros by using the TEXT and REPT functions, or by simply changing the format of the number. This step by step tutorial will assist all levels of Excel users in padding a number with zeros in three different methods.
Figure 1. Final result: Pad a number with zeros
Method 1 formula: =TEXT(B4,"0000000")
Method 2 formula: =TEXT(B4,REPT("0",7))
Method 3: Using the Custom format “0000000”
Syntax of the TEXT Function
TEXT converts a number into text
=TEXT(value, format_text)
- value – any numeric value, a formula or cell reference to a numeric value
- format_text – a numeric format expressed as a text string and enclosed in quotation marks
Syntax of the REPT Function
REPT repeats text for a specified number of times
=REPT(text, number_times)
- text – the text we want to repeat
- number_times – the number of times to repeat the text; must be a positive number
Setting up our Data
Our data is the number “2468” in cell B4. We want to pad this number with zeros such that the resulting value has seven digits. We will record the padded number in cell D4.
In this example, we will learn to pad a number with zeros by using three different methods:
- Using TEXT function
- Using TEXT and REPT functions
- Customizing the format
Figure 2. Sample data to pad a number with zeros
Pad a number with zeros using TEXT function
Padding a number with zeros using the TEXT function can be done in three easy steps:
Step 1. Select cell D4
Step 2. Enter the formula: =TEXT(B4,"0000000")
Step 3. Press ENTER
Figure 3. Entering the formula using TEXT function
Our formula converts the number in cell B4 into a text with the format “0000000”, which means 7 digits. In this case, the number we want to convert has fewer digits than the zeros in the format. Hence, TEXT displays insignificant zeros to be able to return seven digits as per our given format.
As a result, the padded number in cell D4 is “0002468”.
Pad a number with zeros using TEXT and REPT function
There is an alternative solution to padding a number with zeros by combining the TEXT and REPT functions in one formula. Let us follow these steps:
Step 1. Select cell D4
Step 2. Enter the formula: =TEXT(B4,REPT("0",7))
Step 3. Press ENTER
Figure 4. Entering the formula using TEXT and REPT functions
Our formula converts the number in cell B4 into a text with the given format. Our format s determined by the REPT function: REPT(“0”,7). The REPT formula is another way to represent the format we want. In this case, REPT returns a text string with the digit zero “0” repeated seven times “0000000”, which is the same as in the previous example.
As a result, the padded number in cell D4 is “0002468”.
The REPT function is especially useful when the number of digits we want for our padded number is variable. We can link the argument number_times with a cell reference or a formula.
Pad a number with zeros by custom format
The first two methods we have learned both convert the number into a text value. When we want our padded number to remain as a numeric value, we can simply customize the format by following these steps:
Step 1. Select cell D4
Step 2. Enter our value “2468”.
Step 3. Press Ctrl + 1 to launch the Format Cells dialog box.
Step 4. In the Format Cells preview pane, select Custom.
Step 5. In the Type box, enter the format “0000000”
Step 6. Press Enter
Figure 5. Customizing the format to pad a number with zeros
The resulting number still has the value “2468” but it is displayed as “0002468”, which is the same as in the previous methods.
Figure 6. Output: Pad a number with zeros by using custom format
The table below shows the three methods having the same final result.
Figure 7. Three different methods to pad a number with zeros
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment