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.

 

[Peter_SSs]

This formula blows my mind!

Just wondering if you tried either from post #7 that don't even need to be copied down the column?