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

In Excel: 12 OAK ST + APT 5 becomes 12 OAK ST APT 5.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The street line."12 OAK ST"
2IF(B8="",…)Adds a space and the unit, if there is one."12 OAK ST APT 5"
3TRIM( … )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.

  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 and units into the green columns of the Template tab. Leave the unit empty when there isn't one.
  3. 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