Read the street before its unit type

Turn 12 Oak St APT 5 into 12 Oak St. Keep the unit in its own column first.

What you'll make

In Excel: 12 Oak St APT 5 + APT becomes 12 Oak St.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXTBEFORE(A8," "&B8&" ")Keeps what comes before the unit type, as its own word."12 Oak St"
2"Review unit marker"What the cell says when the unit type isn't found.Not used for row 8
3IFERROR( … )Uses part 2 when the unit type isn't found."12 Oak St"

The unit type must match exactly, capitals included, with a space on each side.

=IFERROR(TEXTBEFORE(A8," "&B8&" "),"Review unit marker")

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 delivery lines into column A and their unit types into column B of the Template tab. The streets fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Save the unit in its own column before you replace the delivery line. Fix lines like APT6 by hand.

Microsoft's help: TEXTBEFORE function