Restore a five-digit ZIP from a number
Turn a ZIP that lost its zero, like 2108, back into the text 02108.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The ZIP as a number, with its zero lost. | 2108 |
| 2 | "00000" | The pattern: always five digits. | "00000" |
| 3 | TEXT( … ) | Adds zeros in front until there are five. | "02108" |
The result is text, so Excel keeps the leading zero.
=TEXT(A8,"00000")How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own addresses. Paste ZIP numbers into the green column of the Template tab. The five-digit ZIPs fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this only on a column you know holds five-digit U.S. ZIPs. It can't tell if a ZIP is real.
Microsoft's help: TEXT function
OTHER ROUTES YOU MIGHT LIKE

Display a ZIP+4 from a nine-digit number
21081234 → 02108-1234
Get the template →
Join a ZIP and an optional +4 extension
02108 + 1234 → 02108-1234
Get the template →
Read the five-digit ZIP from a ZIP+4
02108-1234 → text 02108
Get the template →
Keep the zero in a ZIP Code like 02108
2108 → 02108
Take this route →