Remove a duplicated unit designator
Turn APT and APT 5 into APT and 5, so the label prints APT once.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | LEFT(…)=UPPER(A8)&" " | Checks if the value starts with APT and a space. | Yes |
| 2 | TRIM(MID( … )) | Keeps what comes after APT and the space. | "5" |
| 3 | B8 | The value as typed, when it doesn't start that way. | Not used for row 8 |
| 4 | IF( … ) | Shows part 2 when the check says yes, otherwise part 3. | "5" |
Read it left to right. Capitals don't matter for the check, so apt 5 counts too.
=IF(LEFT(UPPER(TRIM(B8)),LEN(A8)+1)=UPPER(A8)&" ",TRIM(MID(TRIM(B8),LEN(A8)+2,LEN(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 each unit designator and unit value into the green columns of the Template tab. The clean values fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
APT4 with no space is left as it is. Fix those by hand first.
Microsoft's help: IF function
OTHER ROUTES YOU MIGHT LIKE

Replace # unit marker with the unit type
12 Oak St #5 + APT → 12 Oak St APT 5
Get the template →
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 →
Remove the empty line when there's no apartment
No apartment → no empty address line
Take this route →