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

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | OR(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 |
| 3 | A8&IF(B8="",…) | The street, then a space and the suite, if any. | "12 Oak St STE 200" |
| 4 | &" PMB "&C8 | Adds PMB and the customer's box number. | "12 Oak St STE 200 PMB 45" |
| 5 | IF( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- 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.
- 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
OTHER ROUTES YOU MIGHT LIKE

Label a box or mailstop number by role
Postal box + 123 → PO BOX 123
Get the template →
Keep both parts of a suite and PMB field
#8 PMB 112 → Facility secondary: #8; customer PMB: 112
Get the template →
Join building, floor and room codes
A + 02 + 004B → BLDG A FL 02 RM 004B
Get the template →
Remove the empty line when there's no apartment
No apartment → no empty address line
Take this route →