Splitting Forenames and Surname

[guerillaexcel]

Morning, I have an extensive list of names that I need to split into forenames and surnames.
I have attached a sample spreadsheet.
I have tried to use the text - to - columns function but this splits each name into a separate cell which is not what I am looking for.

To clarify I want
Mr John Paul Murphy to become
Mr
John Paul
Murphy
in the same row

Any ideas?
Thanks

[guerillaexcel]

Or, if i can just extract the surname then that would be helpful? Possibly a formula with =RIGHT?

[Domski]

Try these:

In B1 copied down: =LEFT(A1,SEARCH(" ",A1)-1)
In C1 copied down: =TRIM(MID(A1,SEARCH(" ",A1),LEN(A1)-LEN(B1)-LEN(D1)))
In D1 copied down: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))

Dom

[guerillaexcel]

Hi Dom, that works perfectly for all names with a title, apologies for scope creep here, but how do i handle those names that don't have a mr or mrs in their name?
e.g.
John P Butler
Mary Anne Lloyd-Evan

[guerillaexcel]

Bump. Anyone able to help?

[Domski]

Try:

In B1: =IF(ISNUMBER(MATCH(LEFT(A1,SEARCH(" ",A1)-1),{"Mr","Mrs","Miss","Ms"},0)),LEFT(A1,SEARCH(" ",A1)-1),"")
In C1: =TRIM(MID(A1,LEN(B1)+1,LEN(A1)-LEN(B1)-LEN(D1)))
In D1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))

Dom

[guerillaexcel]

You are a gentleman and scholar Dom! That's fantastic, thanks very much!