Excel drops the leading zero from a ZIP like 02134 the moment it is typed as a number, and mailing lists often mix five-digit and ZIP+4 values in one column. This recipe pads short ZIPs back to five characters with REPT and cuts longer ones to five with LEFT, leaving a consistent text ZIP.
2134 becomes 02134 and 75201-1234 becomes 75201.
The example
Four ZIPs in different shapes. The padded ones gain characters; the ZIP+4 value loses its extension.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Case | ZIP as entered | Clean ZIP | Chars changed |
| 2 | Boston | 2134 | 02134 | 1 |
| 3 | Dallas | 75201-1234 | 75201 | -5 |
| 4 | Hartford | 6103 | 06103 | 1 |
| 5 | Austin | 78701 | 78701 | 0 |
The formula
Check the length; pad if short, trim if long:
How it works
Here is the logic, one piece at a time:
LEN(B2)<5detects ZIPs that lost their leading zeros.REPT("0",5-LEN(B2))builds exactly the number of zeros needed.&B2glues the zeros in front, keeping the result as text.LEFT(B2,5)keeps only the first five characters of longer ZIPs, removing the ZIP+4 extension.
Format the result column as Text before pasting values, or Excel will strip the zero again.
Try it: interactive demo
Type a ZIP code (any length).
Variations
Strip spaces and dashes first
Clean stray characters before measuring length.
Use TEXT for numeric ZIPs
If the ZIPs are true numbers, TEXT does the padding.
Pitfalls & errors
If the source ZIP is a real number, Excel has already dropped the zero; the value 2134 looks like four characters, so this formula pads it correctly, but the original is a number and displays 2134 until the result is formatted as Text.
Canadian and UK postal codes do not fit this pattern; apply it only to US ZIPs.
Add a data validation rule on the clean column that only allows five-character text.
Practice workbook
Frequently asked questions
Will this keep the ZIP as text?
What about ZIPs with spaces?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class