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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
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,802
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,129,330
Messages
5,635,647
Members
416,870
Latest member
rikimon2

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