Need formula to split names/column

I have a column with names in it. I need to split them into two columns, however I can't use Text to Columns because the names are all formatted differently:

John Smith
John & Melinda Smith
John Harold Smith
John Harold Abel Smith

The above example needs to look like this:

John	Smith
John & Melinda	Smith
John	Harold Smith
John	Harold Abel Smith

I need a way to break it up on the following conditions:

- If the cell contains an & symbol, break the column after the third space - FirstName & PartnerFirstName | LastName
- If the cell does not contain an & symbol, break it after the first space.

When I asked this question awhile ago, I had someone give me this formula:

B1:
=IF(ISNUMBER(SEARCH("&",$A3)),TRIM(MID(SUBSTITUTE($A3," ",REPT(" ", 100)),1,300)),TRIM(MID(SUBSTITUTE($A3," ",REPT(" ", 100)),1,100)))

C1:
=IF(ISNUMBER(SEARCH("&",$A2)),TRIM(MID(SUBSTITUTE($A2," ",REPT(" ", 100)),300,100)),TRIM(MID(SUBSTITUTE($A2," ",REPT(" ", 100)),100,100)))

Unfortunately on names such as 'John Harold Smith' (3 or more word names without '&' symbols) were formatted incorrectly. The above example turned into:

John Charles

Any help would be appreciated! Thanks

Not sure but try this, assuming your name on Column A:

Split 1 (put on Cell C1):
=IF(ISNUMBER(SEARCH("&",$A1)),LEFT(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)),LEFT(A1,SEARCH(" ",A1,1)-1))

Split 2 (put on cell D1):
=SUBSTITUTE(A1,C1,"")

and copied down as necessary