Keep a unit on the street delivery line
Turn 12 OAK ST and APT 5 into 12 OAK ST APT 5 for mailing labels.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The street line. | "12 OAK ST" |
| 2 | IF(B8="",…) | Adds a space and the unit, if there is one. | "12 OAK ST APT 5" |
| 3 | TRIM( … ) | Removes extra spaces. | "12 OAK ST APT 5" |
An empty unit adds nothing, so there is no gap at the end.
=TRIM(A8&IF(B8="",""," "&B8))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 street lines and units into the green columns of the Template tab. Leave the unit empty when there isn't one.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
The unit is kept exactly as typed. Type it with its label, like APT 5 or STE 200.
Microsoft's help: TRIM function
OTHER ROUTES YOU MIGHT LIKE

Write an address as a single sentence
Street, city, state, ZIP → 12 OAK ST, BOSTON, MA 02108
Get the template →
Build a PO Box delivery line
123 → PO BOX 123
Get the template →
Build a rural-route and box line
Route 2 plus box 145 → RR 2 BOX 145
Get the template →
Remove the empty line when there's no apartment
No apartment → no empty address line
Take this route →