Read a mailstop from a company field

Turn Acme Co. | MAILSTOP: 4B-210 into 4B-210, kept as text.

What you'll make

In Excel: Acme Co. | MAILSTOP: 4B-210 becomes 4B-210.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXTAFTER(A8," | MAILSTOP: ")Keeps the text after the | MAILSTOP: label."4B-210"
2"Review mailstop label"What the cell says when the label is missing.Not used for row 8
3IFERROR( … )Uses part 2 when there is no MAILSTOP label."4B-210"

The label must match exactly, spaces and capitals included.

=IFERROR(TEXTAFTER(A8," | MAILSTOP: "),"Review mailstop label")

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 company fields into the green column of the Template tab. The mailstops fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Only the exact label | MAILSTOP: is read. Fix Mailstop: or MS by hand first.

Microsoft's help: TEXTAFTER function