Pad a number with zeros

=TEXT(number,padding)
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")
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:
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.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.