Pad a number with zeros

To pad a number with zeros, you can use a simple formula based on the TEXT function. To pad with a variable number of zeros, you can add the REPT function.

In the example show, the formula in D6 is:

=TEXT(B6,"000000")

How this formula works

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.

In this case, the TEXT function is used to apply a number format that consists only of zeros: "00000", "000000", "0000000", etc. These number formats automatically format a number by adding zeros to the left as needed to reach the total number of zeros supplied.

Note: padding a number with zeros this way changes the number into text, which may or may not suit your needs. To simply display a number with padding, see below.

Pad for display only

To pad a number with zeros for display only, just select the cell(s) and apply a custom number format like so:

Format cells > Number > Custom > "00000"

With this approach, the number is not converted to text, but remains a true number.

Pad with a variable number of zeros

If you want to pad a number with text using a variable number of zeros, you can add the REPT function inside TEXT like this:

=TEXT(B6,REPT("0",C5))  // formula in F5

The REPT function repeats text a given number of times. You can either hard-code the "number times" argument (as above) or pick it up from elsewhere on the worksheet.