How to add a variable number of leading zeroes in Excel?

Question

I have a spreadsheet with 10,000 rows, and in one column, I have an incrementing value:

• 1
• 2
• 3
• ...
• 10000

Now I want to format this so that the cell with the value is always 32 characters long by padding it with zeroes. So, if the value is 1, I want to add 31 leading zeroes, while if the value is 10, I want to add 30 leading zeroes.

This article suggests adding leading zeroes by formatting the numbers as text, which I've done before, but it won't work in this case because the number of zeroes to add is variable. (I could group the rows by number of digits and format them that way, but if possible I would like to come up with a single formula/method to handle any value under 32 characters.)

So how can I add leading zeroes to pad a number to a fixed length?

Answer 1

Do this:

• Select the column
• Right-click any cell inside and select "Format Cells..."
• Select "Custom" on the left
• Enter 32 zeroes in the Type field
• Click OK.