Put one space around a hash unit marker

Turn 12 Oak St #5 into 12 Oak St # 5. The unit stays as typed.

What you'll make

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

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TRIM(TEXTBEFORE(A8,"#"))Keeps the street before the #, without extra spaces."12 Oak St"
2&" # "&Adds the # with one space on each side."12 Oak St # "
3TRIM(TEXTAFTER(A8,"#"))Adds the unit after the #, without extra spaces."12 Oak St # 5"
4"Review # unit marker"What the cell says when the line has no #.Not used for row 8
5IFERROR( … )Uses part 4 when there is no #."12 Oak St # 5"

Only the first # counts. The unit itself is kept as typed.

=IFERROR(TRIM(TEXTBEFORE(A8,"#"))&" # "&TRIM(TEXTAFTER(A8,"#")),"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 delivery lines into the green column of the Template tab. The spaced 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 on lines where # marks the unit. A # inside a firm name would be spaced too.

Microsoft's help: TEXTBEFORE function