Build a country-and-postal key
Join a country code you were given and a postal code into one key for matching.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | UPPER(TRIM(A8)) | Takes the country code, without extra spaces, in capitals. | "US" |
| 2 | &"|"& | Adds a | line between the two parts. | "US|" |
| 3 | UPPER(SUBSTITUTE(B8," ","")) | Adds the postal code with spaces taken out, in capitals. | "US|02108" |
Read it left to right. The country stays in the key, so US|02108 and DE|02108 never match.
=UPPER(TRIM(A8))&"|"&UPPER(SUBSTITUTE(B8," ",""))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 country codes and postal codes into the green columns of the Template tab. The keys fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use the key for matching only, not on envelopes. It trusts the country code, so check those first.
Microsoft's help: UPPER function
OTHER ROUTES YOU MIGHT LIKE

Add a separator after a set digit
021081234 + 5 + - → 02108-1234
Get the template →
Combine ZIP and +4 numbers as text
1002 + 0 → 01002-0000
Get the template →
Add one ATTN prefix without doubling it
Ava Reed or ATTN Ava Reed → ATTN Ava Reed
Get the template →
Keep the zero in a ZIP Code like 02108
2108 → 02108
Take this route →