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

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TEXTBEFORE(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 |
| 3 | IFERROR( … ) | 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.
- 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 streets fill in by themselves.
- 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
OTHER ROUTES YOU MIGHT LIKE

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 →
Keep both parts of a suite and PMB field
#8 PMB 112 → Facility secondary: #8; customer PMB: 112
Get the template →
Remove the empty line when there's no apartment
No apartment → no empty address line
Take this route →