Keep number and text ZIPs as text
Turn the number 2108 into 02108, and keep text ZIPs like 01002-4410 as they are.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8="" | Checks whether the cell is empty. | No, it has a number |
| 2 | ISNUMBER(A8) | Checks whether the ZIP is a number. | Yes, 2108 |
| 3 | TEXT(A8,"00000") | Adds zeros in front until there are five. | "02108" |
| 4 | IF(ISNUMBER( … ) … ) | Uses part 3 for a number, or keeps text as it is. | "02108" |
| 5 | IF( … ) | Leaves an empty cell blank. | "02108" |
Read it left to right. Text ZIPs pass through unchanged, so only numbers get their zeros back.
=IF(A8="","",IF(ISNUMBER(A8),TEXT(A8,"00000"),A8))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 your ZIPs, numbers or text, into the green column of the Template tab. The ZIP codes fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Paste only ZIPs you've already checked. The formula puts zeros back on numbers but doesn't fix text.
Microsoft's help: TEXT function
OTHER ROUTES YOU MIGHT LIKE

Read a three-character ZIP prefix
02108 → text 021
Get the template →
Read the ZIP from a nine-digit number
21081234 → text 02108
Get the template →
Remove a US- prefix from a ZIP
US-02108 → 02108; CA-K1A 0B1 → Review country prefix
Get the template →
Put the street back in an Address Block
Ava Reed, Boston → Ava Reed, 12 Main St, Boston
Take this route →