Abbreviate only the final street suffix

Turn 12 COURT STREET into 12 COURT ST. Earlier words stay spelled out.

What you'll make

In Excel: 12 COURT STREET + STREET, ST becomes 12 COURT ST.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXTAFTER( … )=B8Checks the last word is the full suffix in column B.TRUE
2TEXTBEFORE( … )&" "&C8Keeps 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
4IF( … )Shows the shortened line, or else the message."12 COURT ST"
5IFERROR( … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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