Choose postal-code spacing by country

Format US, Canadian and UK codes by the country code you give, never by guessing.

What you'll make

In Excel: 021081234 + US becomes 02108-1234; k1a0b1 + CA becomes K1A 0B1.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1UPPER(SUBSTITUTE(A8," ",""))Takes out spaces and makes capitals. LET calls this p."021081234"
2UPPER(B8)Reads the country code in capitals."US"
3"US",IF( … )US: adds a hyphen after five of nine digits."02108-1234"
4"CA", … "GB", …CA and GB: one space before the last three.Not used for row 8
5LET( … )Works out p once, then picks the country's layout."02108-1234"

Read it left to right. SWITCH picks the layout; any other country says Review country.

=LET(p,UPPER(SUBSTITUTE(A8," ","")),SWITCH(UPPER(B8),"US",IF(LEN(p)=9,LEFT(p,5)&"-"&RIGHT(p,4),p),"CA",LEFT(p,3)&" "&RIGHT(p,3),"GB",LEFT(p,LEN(p)-3)&" "&RIGHT(p,3),"Review country"))

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 postal codes and their country codes (US, CA or GB) into the green columns of the Template tab. The formatted codes fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

This only adds spaces and hyphens. It trusts the country code, so check each code and country first.

Microsoft's help: SWITCH function