Split First and Last Names, removing any middle names

Ronanm

Board Regular
Joined
Nov 13, 2010
Messages
107
Hi

I need to split First and Last Names, removing any middle names as per the table below;

A1
B1
Currently
Becomes
Walker Barbara Ann
Barbara Walker
Taylor Ron James
Ron Taylor
Clark Steve
Steve Clark
Price Kevin Liam Pat
Kevin Price

<tbody>
</tbody>


<tbody>
</tbody>
Thanks
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,017
Office Version
  1. 365
Platform
  1. Windows
One way:
Code:
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100)) & " " & TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",100)),100))
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825
You can also use Flash Fill. To do so, put your list in Column A, then for 1 or 2 cells, manually put in the desired result. I had to put in B1 and B3. Then you can select a cell in column B and press Control+E. Excel will try to fill in the rest of the column, using your example(s) as a model.

However, whichever method you use, be aware that some names just don't parse easily. I have a sister-in-law named Jayne Ann, and you do NOT call her Jayne! So first names like Mary Jo, or last names like St. James, could both cause you problems.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
A little shorter:
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),255,255))&LEFT(" "&A1,FIND(" ",A1&" "))
 

Ronanm

Board Regular
Joined
Nov 13, 2010
Messages
107
Wow. Thank you all so much. Joe 4 Eric W and Scott Huish. Appreciate it. I'll go with the last one as I can investigate all the formulas together :)

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,130,128
Messages
5,640,281
Members
417,133
Latest member
caaronh85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top