Split names into two columns: 1st column has 1st and middle name; 2nd column has the surname.

[maxim642]

Full name	First & Middle	Surname
John Jack Murphy	John Jack	Murphy
Mary-Joe Spencer	Mary-Joe	Spencer
Jordan Peters	Jordan	Peters
Jamie John-Paul	Jamie	John-Paul

The tricky part is where some names have a hyphen (so they would be the first name), or other entries that don't have a middle name.

The above table provides examples of how it should look. I just don't know the formula to achieve it in the last two columns.

 

[Trebor76]

Hi maxim642,

Based on the assumption you need to find the position of the last space in a string, I adapted the formula from here to come up with the following (assumes the data starts in cell A2):

First & Middle Names: =TRIM(LEFT(A2,FIND("@",SUBSTITUTE(A2," ","@",(LEN(A2)-LEN(SUBSTITUTE(A2," ","")))/LEN(" ")))))
Surname: =TRIM(MID(A2,FIND("@",SUBSTITUTE(A2," ","@",(LEN(A2)-LEN(SUBSTITUTE(A2," ","")))/LEN(" "))),255))

Regards,

Robert

 

[Fluff]

Could you have names like "Jamie John Paul" where John Paul is the surname but doesn't have a hyphen?

 

[maxim642]

Could you have names like "Jamie John Paul" where John Paul is the surname but doesn't have a hyphen?

No, thankfully any double-barrel surnames/middle names are denoted with a hyphen. So, Jamie John Paul would be Jamie John (2nd column) Paul (3rd column).

 

[maxim642]

Hi maxim642,

Based on the assumption you need to find the position of the last space in a string, I adapted the formula from here to come up with the following (assumes the data starts in cell A2):

First & Middle Names: =TRIM(LEFT(A2,FIND("@",SUBSTITUTE(A2," ","@",(LEN(A2)-LEN(SUBSTITUTE(A2," ","")))/LEN(" ")))))
Surname: =TRIM(MID(A2,FIND("@",SUBSTITUTE(A2," ","@",(LEN(A2)-LEN(SUBSTITUTE(A2," ","")))/LEN(" "))),255))

Regards,

Robert

This is great, thank you.

In layman's terms, what exactly is this formula doing? And what is the significance of the 255 value in the Surname formula? Thanks again for your help, it solved the issue.

 

[Fluff]

No, thankfully any double-barrel surnames/middle names are denoted with a hyphen.

Ok, a similar approach, but this will spill across.

Fluff.xlsm
	A	B	C
1	Full name	First & Middle	Surname
2	John Jack Murphy	John Jack	Murphy
3	Mary-Joe Spencer	Mary-Joe	Spencer
4	Jordan Peters	Jordan	Peters
5	Jamie John-Paul	Jamie	John-Paul
Data

Cell Formulas
Range	Formula
B2:C5	B2	=LET(m,FIND("^",SUBSTITUTE(A2," ","^",(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),CHOOSE({1,2},LEFT(A2,m-1),MID(A2,m+1, LEN(A2))))
Dynamic array formulas.

 

[Peter_SSs]

Ok, a similar approach, but this will spill across.

.. or it could spill both down and across per the top section below with just a single formula in B2, or if the latest text-splitting functions are available then the shorter formula in B8 below will also spill down and across.

22 06 07.xlsm
	A	B	C
1	Full name	First & Middle	Surname
2	John Jack Murphy	John Jack	Murphy
3	Mary-Joe Spencer	Mary-Joe	Spencer
4	Jordan Peters	Jordan	Peters
5	Jamie John-Paul	Jamie	John-Paul
6
7	Full name	First & Middle	Surname
8	John Jack Murphy	John Jack	Murphy
9	Mary-Joe Spencer	Mary-Joe	Spencer
10	Jordan Peters	Jordan	Peters
11	Jamie John-Paul	Jamie	John-Paul
Split Names

Cell Formulas
Range	Formula
B2:C5	B2	=LET(r,A2:A5,s,TRIM(RIGHT(SUBSTITUTE(r," ",REPT(" ",50)),50)),CHOOSE({1,2},LEFT(r,LEN(r)-LEN(s)-1),s))
B8:C11	B8	=CHOOSE({1,2},TEXTBEFORE(A8:A11," ",-1),TEXTAFTER(A8:A11," ",-1))
Dynamic array formulas.

 

[maxim642]

Ok, a similar approach, but this will spill across.

Fluff.xlsm
	A	B	C
1	Full name	First & Middle	Surname
2	John Jack Murphy	John Jack	Murphy
3	Mary-Joe Spencer	Mary-Joe	Spencer
4	Jordan Peters	Jordan	Peters
5	Jamie John-Paul	Jamie	John-Paul
Data

Cell Formulas
Range	Formula
B2:C5	B2	=LET(m,FIND("^",SUBSTITUTE(A2," ","^",(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),CHOOSE({1,2},LEFT(A2,m-1),MID(A2,m+1, LEN(A2))))
Dynamic array formulas.

Thanks, this also works. This formula blows my mind! Great stuff!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[alansidman]

Here is an alternative means to your end with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Full name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Full name.1", "Full name.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Full name.1", "First Middle"}, {"Full name.2", "Last"}})
in
    #"Renamed Columns"