Split First and Last Names, removing any middle names

[Ronanm]

Hi

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

[TABLE="class: grid, width: 500"] <tbody>[TR] [TD]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>

[/TD]
[/TR]
</tbody>[/TABLE]
Thanks

 

[Joe4]

One way:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100)) & " " & TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",100)),100))

 

[Eric W]

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]

A little shorter:
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),255,255))&LEFT(" "&A1,FIND(" ",A1&" "))

 

[Ronanm]

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