put data in its own column using formulas

[johnmerlino]

Hey all,

I have a formula:

Please Login or Register  to view this content.

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.

[Marcol]

I'm sure there will be a more elegant way but by replacing "No" in your formula
with
SUBSTITUTE(A3,MID(A3,FIND(",",A3,1)+1,FIND("&",A3,1)-FIND(",",A3,1)),"")

Combined gives

Please Login or Register  to view this content.

Hope this helps

[johnmerlino]

Thanks for response. I will test it out.

[johnmerlino]

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

[johnmerlino]

I seems this worked for me:

Please Login or Register  to view this content.

[Marcol]

A quick fix

Please Login or Register  to view this content.

There has to be a better way.