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?


1 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.

enter image description here

enter image description here

  • LibreOffice: "Custom" = "User-Defined" all other is the same.
    – Hannu
    Feb 5 at 21:02
  • Note: The original question only uses numbers from 1 to 10000, but if you ever need to store very large numbers, be aware Excel can only store 15 significant digits of a number. For example, if you enter 11111111111111111111111111111111 in the custom format column used in this answer, you'll get 11111111111111100000000000000000. If you ever need to store an ID number with 16 or more digits (i.e. you don't need to calculate with it), you'll probably have to use some kind of text method.
    – Bavi_H
    Feb 6 at 0:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.