Need formula to split names/column

[Sm3tUser]

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

<tbody>
</tbody>

The above example needs to look like this:

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

<tbody>
</tbody>

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

<tbody>
</tbody>

Any help would be appreciated! Thanks

 

[Rick Rothstein]

Assuming your names start in cell A1, put these formulas in the indicated cells and copy down...

B1: =TRIM(LEFT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",200)),(1+2*(COUNTIF(A1,"*&*")>0))*200))

C1: =MID(A1,LEN(B1)+1,200)