Replace # unit marker with the unit type

Turn 12 Oak St #5 into 12 Oak St APT 5 using your confirmed unit type.

What you'll make

In Excel: 12 Oak St #5 + APT becomes 12 Oak St APT 5.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXTBEFORE(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 "
3TRIM(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
5IFERROR( … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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