Build a delivery line with a suite and PMB

Turn 12 Oak St, STE 200 and box 45 into 12 Oak St STE 200 PMB 45.

What you'll make

In Excel: STE 200 + 45 becomes 12 Oak St STE 200 PMB 45.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(A8="",C8="")Checks if the street or box number is empty.No, both are filled
2"Review street or mailbox"Shown when the street or box number is missing.Not used for row 8
3A8&IF(B8="",…)The street, then a space and the suite, if any."12 Oak St STE 200"
4&" PMB "&C8Adds PMB and the customer's box number."12 Oak St STE 200 PMB 45"
5IF( … )Uses part 2 if part 1 finds a gap, else parts 3 and 4."12 Oak St STE 200 PMB 45"

Read it left to right. An empty suite adds nothing, so there is no extra space.

=IF(OR(A8="",C8=""),"Review street or mailbox",A8&IF(B8="",""," "&B8)&" PMB "&C8)

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, facility suites and box numbers into the green columns of the Template tab. Leave the suite 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

Keep the suite and the box number in their own columns. Never join them into one number.

Microsoft's help: IF function