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

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | SEARCH(" "&B8&" "," "&A8) | Finds where the particle starts, as a whole word. | 5 |
| 2 | MID(A8, … ,LEN(A8)) | Keeps everything from there to the end. | "de la Cruz" |
| 3 | TRIM( … ) | Removes any spaces left at the ends. | "de la Cruz" |
| 4 | IFERROR( … ,"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.
- See it work. The Example tab shows 20 made-up names with the formula already in.
- 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.
- 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
OTHER ROUTES YOU MIGHT LIKE

Keep the last two words of a name
Ava Reed Park → Reed Park
Get the template →
Keep the first two words of a name
Mary Ann Reed → Mary Ann
Get the template →
Put a known compound surname first
Nia van Dijk + van Dijk → van Dijk, Nia
Get the template →
Fix "Dear ," when a first name is blank
(blank) → Dear colleague,
Take this route →