Keep both parts of a suite and PMB field
Turn STE 200 PMB 004 into two labeled parts, so no number is lost.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | "Facility secondary: "&TEXTBEFORE(A8," PMB ") | Labels the suite, the part before PMB. | "Facility … STE 200" |
| 2 | &"; customer PMB: "&TEXTAFTER(A8," PMB ") | Adds the labeled mailbox number after PMB. | "… customer PMB: 004" |
| 3 | "Review PMB field" | What the cell says when PMB isn't found. | Not used for row 8 |
| 4 | IFERROR( … ) | Uses part 3 when PMB isn't found. | "… customer PMB: 004" |
Each label is joined in with its part. PMB must be in capitals with a space on each side.
=IFERROR("Facility secondary: "&TEXTBEFORE(A8," PMB ")&"; customer PMB: "&TEXTAFTER(A8," PMB "),"Review PMB field")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 suite and PMB fields into column A of the Template tab. Both parts fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Keep the street and the recipient in their own columns. Fix fields like PMB004 by hand.
Microsoft's help: TEXTBEFORE function
OTHER ROUTES YOU MIGHT LIKE

Join building, floor and room codes
A + 02 + 004B → BLDG A FL 02 RM 004B
Get the template →
Remove a duplicated unit designator
APT + APT 5 → 5
Get the template →
Replace # unit marker with the unit type
12 Oak St #5 + APT → 12 Oak St APT 5
Get the template →
Remove the empty line when there's no apartment
No apartment → no empty address line
Take this route →