Replace # unit marker with the unit type
Turn 12 Oak St #5 into 12 Oak St APT 5 using your confirmed unit type.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TEXTBEFORE(A8," #",-1) | Keeps the street before the last space and #. | "12 Oak St" |
| 2 | &" "&B8&" "& | Adds the unit type from column B, with spaces. | "12 Oak St APT " |
| 3 | TRIM(TEXTAFTER(A8," #",-1)) | Adds the unit number after the #. | "12 Oak St APT 5" |
| 4 | "Review unit marker" | What the cell says when there is no space and #. | Not used for row 8 |
| 5 | IFERROR( … ) | Uses part 4 when parts 1 and 3 find no #. | "12 Oak St APT 5" |
Only the last # with a space before it counts.
=IFERROR(TEXTBEFORE(A8," #",-1)&" "&B8&" "&TRIM(TEXTAFTER(A8," #",-1)),"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 street lines into column A and their unit types into column B of the Template tab. The new lines fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this only when the # marks an apartment or suite, not a private mailbox. Type the unit type you confirmed; don't guess APT.
Microsoft's help: TEXTBEFORE function
OTHER ROUTES YOU MIGHT LIKE

Put one space around a hash unit marker
12 Oak St #5 → 12 Oak St # 5
Get the template →
Read a unit value after its unit type
12 Oak St APT 5 + APT → 5
Get the template →
Read the street before its unit type
12 Oak St APT 5 + APT → 12 Oak St
Get the template →
Remove the empty line when there's no apartment
No apartment → no empty address line
Take this route →