Abbreviate only the final street suffix
Turn 12 COURT STREET into 12 COURT ST. Earlier words stay spelled out.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TEXTAFTER( … )=B8 | Checks the last word is the full suffix in column B. | TRUE |
| 2 | TEXTBEFORE( … )&" "&C8 | Keeps the words before it, then adds the short suffix. | "12 COURT ST" |
| 3 | "Review: suffix does not match" | The message when the last word isn't the suffix. | Not used for row 8 |
| 4 | IF( … ) | Shows the shortened line, or else the message. | "12 COURT ST" |
| 5 | IFERROR( … ) | Shows the other message when there is no space. | "12 COURT ST" |
Only the last word changes, so COURT in 12 COURT STREET stays. Part 2 adds the space too.
=IFERROR(IF(TEXTAFTER(A8," ",-1)=B8,TEXTBEFORE(A8," ",-1)&" "&C8,"Review: suffix does not match"),"Review: no space found")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 street lines, the full suffix and its short form into the green columns of the Template tab. The shortened lines 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 lines that end with the suffix. A unit or direction after it, like E, needs a look first.
Microsoft's help: TEXTAFTER function
OTHER ROUTES YOU MIGHT LIKE

Fix odd hyphens in house numbers
108‑02 → 108-02
Get the template →
Get a house number with a fraction
12 1/2 Oak St → 12 1/2
Get the template →
Return the street after a fraction
12 1/2 Oak St → Oak St
Get the template →
Put the street back in an Address Block
Ava Reed, Boston → Ava Reed, 12 Main St, Boston
Take this route →