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

In Excel: US + 02108 becomes US|02108; CA + m5v 2t6 becomes CA|M5V2T6.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1UPPER(TRIM(A8))Takes the country code, without extra spaces, in capitals."US"
2&"|"&Adds a | line between the two parts."US|"
3UPPER(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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own addresses. Paste country codes and postal codes into the green columns of the Template tab. The keys fill in by themselves.
  3. 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