Remove a known note from names

Turn Ava [VIP] Reed into Ava Reed, with the note you choose in column B.

What you'll make

In Excel: Ava [VIP] Reed + [VIP] becomes Ava Reed.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The name with the note inside it."Ava [VIP] Reed"
2B8The exact note to take out."[VIP]"
3""Nothing, so the note is removed.""
4SUBSTITUTE( … )Removes every copy of the note."Ava Reed"
5TRIM( … )Removes the extra space the note left."Ava Reed"

SUBSTITUTE works first, then TRIM tidies up the spaces around where the note was.

=TRIM(SUBSTITUTE(A8,B8,""))

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up names with the formula already in.
  2. Use your own names. Paste names into column A of the Template tab and the note to remove into column B. Clean names fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

The note must match exactly, capitals included. Check any row where the note is still there.

Route: Get names ready for the letter

Microsoft's help: SUBSTITUTE function