Need a formula/way to split column

[Sm3tUser]

I have a column with first and last names in it. I need to split them into two columns, however I can't use Text to Columns because some of the names are formatted as FirstName LastName, but others as FirstName & PartnerFirstName LastName. 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 - eg FirstName & PartnerFirstName | LastName
- If the cell does not contain an & symbol, break it after the first space.

Any help would be appreciated! Thanks

 

[JoeMo]

Excel Workbook
	A	B	C
1	FirstName & PartnerFirstName LastName	FirstName & PartnerFirstName	LastName
2	FirstName LastName	FirstName	LastName
Sheet36

 

[Rick Rothstein]

Here is another set of formulas you can consider...

B1: =LEFT(A1,LEN(A1)-LEN(C1)-1)

C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),200))

 

[Sm3tUser]

I love you