Normalize a Canadian postal code

Turn a checked code like k1a0b1 into K1A 0B1: capitals, one space in the middle.

What you'll make

In Excel: k1a0b1 becomes K1A 0B1.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEFT(SUBSTITUTE(A8," ",""),3)Removes spaces, then takes the first three characters."k1a"
2&" "&Adds one space after them."k1a "
3RIGHT(SUBSTITUTE(A8," ",""),3)Adds the last three characters, without spaces."k1a 0b1"
4UPPER( … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own addresses. Paste Canadian postal codes into the green column 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

Use this only on codes you know are real six-character Canadian postal codes. It doesn't check them.

Microsoft's help: UPPER function