put data in its own column using formulas

Hey all,

I have a formula:

=IF( ISERROR( SEARCH("&", A1) ), IF( ISERROR( SEARCH(" ", A1) ), A1, LEFT(A1,LOOKUP(2^15,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))-1), "NO" )

Basically, this will convert this:
AVERNA,ROBERT C H/E
to this:
AVERNA,ROBERT C

and if there is no middle initial, then it will leave this:
Corn,Marlin
like this:
Corn,Marlin

But you notice I have a string "NO" at the end. That's because I can't figure one piece of the puzzle out. I want to convert this:
CATANESE,SALVATORE J & OLGA M
or this:
JIMENEZ,ALEJANDRO & HILDA M
to this:
CATANESE,OLGA M
and this:
JIMENEZ,HILDA M
in their own column.

I can't find any solution out there to do what I just specified above. If anyone could lead me in right track, I'd greatly appreciate.

Thanks for response.

Thanks for response. I will test it out.

Thanks for response. Do you know why when this:

CATANESE,SALVATORE J & OLGA M

converts to this

CATANESE, OLGA M

There is a space between the comma and the first name.

Is there a quick solution to get it like this:
CATANESE,OLGA M

where there is no space between comma and first name

I seems this worked for me:

=SUBSTITUTE(SUBSTITUTE(B1,", ",",")," -","-")