Display a ZIP+4 from a nine-digit number
Turn a checked ZIP+4 that lost its zero, like 21081234, into 02108-1234.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The ZIP+4 as a number, with its zero lost. | 21081234 |
| 2 | "00000-0000" | The pattern: nine digits with a hyphen. | "00000-0000" |
| 3 | TEXT( … ) | Adds front zeros, then the hyphen after five. | "02108-1234" |
The result is text, so Excel keeps the leading zero.
=TEXT(A8,"00000-0000")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+4 numbers into the green column of the Template tab. The ZIP+4 codes 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 numbers you know hold all nine digits. A plain five-digit ZIP comes out wrong.
Microsoft's help: TEXT function
OTHER ROUTES YOU MIGHT LIKE

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 →
Read the extension from a ZIP+4
02108-1234 → 1234; ZIP alone → blank
Get the template →
Keep the zero in a ZIP Code like 02108
2108 → 02108
Take this route →