Read the ZIP from a nine-digit number
Turn a checked ZIP+4 that lost its zero, like 21081234, into the ZIP 02108.
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 | TEXT( … ,"000000000") | Puts the lost zero back, making nine digits. | "021081234" |
| 3 | LEFT( … ,5) | Keeps the first five digits, the ZIP. | "02108" |
Each bracket wraps the one above it, so Excel works from the inside out. The result is text, so the leading zero stays.
=LEFT(TEXT(A8,"000000000"),5)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 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 numbers you know held all nine digits. A plain five-digit ZIP comes out wrong.
Microsoft's help: TEXT function
OTHER ROUTES YOU MIGHT LIKE

Remove a US- prefix from a ZIP
US-02108 → 02108; CA-K1A 0B1 → Review country prefix
Get the template →
Restore a five-digit ZIP from a number
2108 → text 02108
Get the template →
Display a ZIP+4 from a nine-digit number
21081234 → 02108-1234
Get the template →
Keep the zero in a ZIP Code like 02108
2108 → 02108
Take this route →