Build a Canadian address line
Join a municipality, province and postal code: OTTAWA ON K1A 0B1.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | UPPER(TRIM(A8)) | The municipality in capitals, without stray spaces. | "OTTAWA" |
| 2 | &" "& | Adds one space. | "OTTAWA " |
| 3 | UPPER(TRIM(B8)) | The province code in capitals, without stray spaces. | "OTTAWA ON" |
| 4 | &" "& | Adds two spaces before the postal code. | "OTTAWA ON " |
| 5 | UPPER(C8) | Adds the postal code in capitals. | "OTTAWA ON K1A 0B1" |
Read it left to right. The postal code keeps its own middle space.
=UPPER(TRIM(A8))&" "&UPPER(TRIM(B8))&" "&UPPER(C8)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 municipalities, two-letter province codes and postal codes into the green columns of the Template tab. The lines fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
The formula doesn't check that a postal code belongs to its province. Give new addresses a quick look.
Microsoft's help: UPPER function
OTHER ROUTES YOU MIGHT LIKE

Build a U.S. city-state-ZIP line
Boston + MA + 02108 → BOSTON MA 02108
Get the template →
Keep a unit on the street delivery line
12 OAK ST + APT 5 → 12 OAK ST APT 5
Get the template →
Write an address as a single sentence
Street, city, state, ZIP → 12 OAK ST, BOSTON, MA 02108
Get the template →
Put the street back in an Address Block
Ava Reed, Boston → Ava Reed, 12 Main St, Boston
Take this route →