Keep both parts of a suite and PMB field

Turn STE 200 PMB 004 into two labeled parts, so no number is lost.

What you'll make

In Excel: #8 PMB 112 becomes Facility secondary: #8; customer PMB: 112.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult 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
4IFERROR( … )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.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own addresses. Paste suite and PMB fields into column A of the Template tab. Both parts fill in by themselves.
  3. 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