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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]John Smith[/TD]
[/TR]
[TR]
[TD]John & Melinda Smith[/TD]
[/TR]
[TR]
[TD]John Harold Smith[/TD]
[/TR]
[TR]
[TD]John Harold Abel Smith[/TD]
[/TR]
</tbody>[/TABLE]

The above example needs to look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]John[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]John & Melinda[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Harold Smith[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Harold Abel Smith[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]John[/TD]
[TD]Charles[/TD]
[/TR]
</tbody>[/TABLE]

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)