Place Canadian street types by language
Turn PRINCIPALE and RUE into RUE PRINCIPALE, and MAIN and ST into MAIN ST.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | OR( … ) | Checks for a missing name or type, or no TRUE/FALSE. | FALSE |
| 2 | IF(C8="English", … ) | English: the name, then the type. | Not used for row 8 |
| 3 | IF(D8, … ) | Number names like 3E come first; otherwise the type does. | "RUE PRINCIPALE" |
| 4 | IF(C8="French", … ) | Uses part 3 for French, else asks for the language. | "RUE PRINCIPALE" |
| 5 | IF( … ) | Shows the first message when part 1 is TRUE. | "RUE PRINCIPALE" |
Works from the inside out. Column D says TRUE when the name is a number, like 3E or 5TH.
=IF(OR(A8="",B8="",NOT(ISLOGICAL(D8))),"Review: check name, type, flag",IF(C8="English",A8&" "&B8,IF(C8="French",IF(D8,A8&" "&B8,B8&" "&A8),"Review: English or French?")))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 the street name, street type, English or French, and TRUE or FALSE into the Template tab's green columns. The names with types fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Type TRUE or FALSE in column D, not yes or no. The formula never translates or guesses the language.
Microsoft's help: IF function
OTHER ROUTES YOU MIGHT LIKE

Build an HC route and box line
STAR ROUTE + 007 + 001-02 → HC 7 BOX 001-02
Get the template →
Abbreviate only the final street suffix
12 COURT STREET + STREET, ST → 12 COURT ST
Get the template →
Fix odd hyphens in house numbers
108‑02 → 108-02
Get the template →
Put the street back in an Address Block
Ava Reed, Boston → Ava Reed, 12 Main St, Boston
Take this route →