Read a unit value after its unit type

Turn 12 Oak St APT 5 into 5. A hyphen in the street number is left alone.

What you'll make

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

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXTAFTER(A8," "&B8&" ")Keeps what comes after the unit type, as its own word."5"
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."5"

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

=IFERROR(TEXTAFTER(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 units fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Type the unit type you confirmed, as the line writes it. Fix lines like APT6 by hand.

Microsoft's help: TEXTAFTER function