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.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TEXTAFTER(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 |
| 3 | IFERROR( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- 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.
- 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
OTHER ROUTES YOU MIGHT LIKE

Read the street before its unit type
12 Oak St APT 5 + APT → 12 Oak St
Get the template →
Build a delivery line with a suite and PMB
STE 200 + 45 → 12 Oak St STE 200 PMB 45
Get the template →
Label a box or mailstop number by role
Postal box + 123 → PO BOX 123
Get the template →
Remove the empty line when there's no apartment
No apartment → no empty address line
Take this route →