How to Add Leading Zeros in Google Sheets
Use =TEXT(A2,"00000") to pad a number to five digits, so 42 becomes 00042. Add or remove 0s in the pattern to set the total width.
=TEXT(A2,"00000")
Each 0 in the pattern is one digit of the fixed width. The result is text, which is exactly what you want for ZIP codes and IDs.
How it works
Google Sheets drops leading zeros from real numbers because 007 and 7 are the same value. TEXT(A2,"00000") converts the number to text padded to a fixed width, so the zeros stick. Use as many 0s as the width you need. To pad to a variable width, combine REPT with the number. Note the result is text, so it won't do math — pad only for display or export.
Variations
Pad to a different width
=TEXT(A2,"000000")
Six 0s pad to six digits; adjust to the length you need.
Pad with REPT to a target length
=REPT("0",6-LEN(A2))&A2
Prepends just enough zeros to reach 6 characters.
Fill the whole column
=ARRAYFORMULA(IF(A2:A="","",TEXT(A2:A,"00000")))
Pads every filled row in one formula.
Examples
| Scenario | Formula |
|---|---|
| 5-digit ZIP code | =TEXT(A2,"00000") |
| Fixed-length invoice ID | =REPT("0",6-LEN(A2))&A2 |
FAQ
Why does Google Sheets remove my leading zeros?
It stores numbers by value, and 007 equals 7. Convert to padded text with =TEXT(A2,"00000") to keep the zeros.
How do I choose the total number of digits?
Use one 0 per digit in the pattern: "00000" is five digits, "000000" is six.
Can I still do math on padded numbers?
No — TEXT returns text. Pad only for display or export; keep the raw number for calculations.