Get a surname starting with a particle

Turn Ava de la Cruz into de la Cruz, using the first word of the surname you know.

What you'll make

In Excel: Ava de la Cruz + de la becomes de la Cruz.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1SEARCH(" "&B8&" "," "&A8)Finds where the particle starts, as a whole word.5
2MID(A8, … ,LEN(A8))Keeps everything from there to the end."de la Cruz"
3TRIM( … )Removes any spaces left at the ends."de la Cruz"
4IFERROR( … ,"Review name")Shows Review name when the particle isn't found."de la Cruz"

Each bracket wraps the one above it, so Excel works from the inside out. Capitals don't matter, so DE LA matches de la.

=IFERROR(TRIM(MID(A8,SEARCH(" "&B8&" "," "&A8),LEN(A8))),"Review name")

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 full names and the particle each surname starts with into the green columns of the Template tab. Particles are words such as de la or van. Surnames fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

The surname runs to the end of the name. Ava de la Cruz Jr. would keep the Jr., so remove suffixes first.

Route: Get names ready for the letter

Microsoft's help: SEARCH function