Combine ZIP and +4 numbers as text
Turn 1002 and 0 into 01002-0000, so a zero extension isn't lost.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TEXT(A8,"00000") | Writes the ZIP as five digits, zeros in front. | "02108" |
| 2 | IF(B8="",…) | Adds a hyphen and four digits, if there's a +4. | "02108-1234" |
Read it left to right. A +4 of 0 becomes 0000. An empty +4 adds nothing.
=TEXT(A8,"00000")&IF(B8="","","-"&TEXT(B8,"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 numbers and +4 numbers into the green columns of the Template tab. Leave the +4 empty when there isn't one.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this only on columns you know hold U.S. ZIPs and +4 parts. It can't tell if a ZIP is real.
Microsoft's help: TEXT function
OTHER ROUTES YOU MIGHT LIKE

Add a separator after a set digit
021081234 + 5 + - → 02108-1234
Get the template →
Add one ATTN prefix without doubling it
Ava Reed or ATTN Ava Reed → ATTN Ava Reed
Get the template →
Extract attention text from an ATTN line
ATTN Ava Reed ¶ 12 Oak St → Ava Reed
Get the template →
Keep the zero in a ZIP Code like 02108
2108 → 02108
Take this route →