As the formula is copied down, the numbers in column B are padded with a progressively larger number of zeros based on the number in column C. Note that the result is a text value.
In this example, the goal is to pad a number with zeros. To illustrate how Excel functions can be combined, the number of zeros to use is variable, and comes from column C. The formula used to solve this problem combines the TEXT function and the REPT function.
The TEXT function returns a number formatted as text, using the number format provided. The TEXT function can apply number formats of any kind to numbers. It is most often used when you want to maintain number formatting for a number when concatenating that number with other text. For example to use the TEXT function to pad a number 3, 4, and 5 zeros:
Padding a number with zeros with the TEXT function changes the number into text, which may not suit your needs. To simply display a number with padding, you can use a regular number format. For example, to pad a number with 5 zeros for display only, select the cells and use the shortcut Control + 1 to open the Format Cells window. Then:
Format cells > Number > Custom > "00000"
With this approach, the number is not converted to text, but remains a true number. More details here.
The TEXT function can apply number formats of any kind, including currency, date, percentage, etc. By applying a number format like "00", "000", "0000", you can "pad" numbers with as many zeros as you like. Zeros will only be added where needed...
In this example, the goal is to add leading zeros to a given number so that the total number of characters displayed is 5. Sometimes this is referred to as "padding" a number with zeros, because the number of zeros needed is variable. If the...
The Excel REPT function repeats characters a given number of times. For example, =REPT("x",5) returns "xxxxx".
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.