Normalize a Canadian postal code
Turn a checked code like k1a0b1 into K1A 0B1: capitals, one space in the middle.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | LEFT(SUBSTITUTE(A8," ",""),3) | Removes spaces, then takes the first three characters. | "k1a" |
| 2 | &" "& | Adds one space after them. | "k1a " |
| 3 | RIGHT(SUBSTITUTE(A8," ",""),3) | Adds the last three characters, without spaces. | "k1a 0b1" |
| 4 | UPPER( … ) | Turns every letter into a capital. | "K1A 0B1" |
Read it left to right. UPPER wraps everything, so the capitals come last.
=UPPER(LEFT(SUBSTITUTE(A8," ",""),3)&" "&RIGHT(SUBSTITUTE(A8," ",""),3))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 Canadian postal codes into the green column of the Template tab. The formatted codes fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this only on codes you know are real six-character Canadian postal codes. It doesn't check them.
Microsoft's help: UPPER function
OTHER ROUTES YOU MIGHT LIKE

Choose postal-code spacing by country
021081234 + US → 02108-1234; k1a0b1 + CA → K1A 0B1
Get the template →
Normalize spacing in valid UK postcodes
sw1a1aa → SW1A 1AA
Get the template →
Remove a US- prefix from a ZIP
US-02108 → 02108; CA-K1A 0B1 → Review country prefix
Get the template →
Keep the zero in a ZIP Code like 02108
2108 → 02108
Take this route →