Separate Values into Column B & C Using Right, Left, Mid, Search, or Substitute function

[bjnockle]

Looking for the correct combination of Right, Left, Search,Mid, or Substitute function to look into column A2:A181 and separate values into column B2:B181 (heading is Split I) and column C2:C181 (heading is Split 2). Desired outcome is in column B2:B181 and C2:C181. See sample file.

Thanks.

[JohnTopley]

in B2

=IFERROR(LEFT(A2,FIND(".,",A2,1)-1),LEFT(A2,FIND(",",A2,1)-1))
in C2

=SUBSTITUTE(A2,B2&",","")

But

De Nault, IV, Smithburg
G.V.K., Sr., Landlonder
Labogina III, Dr, Nowadays

not handled

There no logic which differentiates between cases which 2 commas

James M., Moroney, III with result James M. and Moroney, III

vs

De Nault, IV, Smithburg with result De Nault, IV and Smithburg

[TMS]

@JT: you could convert the first comma to a special character, say "|", and then FIND the "|" for the split. Too tired to look at it now ... been spending a lot of time on another thread and watching the snooker.

[bjnockle]

JohnTopley: Please take a look and see if you are able to apply TMS suggestion to address De Nault, IV, Smithburg, G.V.K., Sr., Landlonder and Labogina III, Dr, Nowadays

Thanks

[JohnTopley]

I did look at TMS's suggestion but it does not resolve the problem. See below

De Nault| IV, Smithburg

G.V.K.| Sr., Landlonder

Labogina III| Dr, Nowadays

I am sorry but for me it is diminishing returns given the (small ?) number of manual interventions that might be required.

[TMS]

Please see the attached updated sample. I have applied the suggestion that I made. There are 13 anomalies (out of 180 entries) where the "rules" do not appear to be consistent. Note that I needed to Trim the manual Split 2 entries for the comparison as many of them included leading spaces.

[TMS]

It looks to me as if the structure of the anomalous names is inconsistent. In some cases, you could get away with removing the first comma but that's not so for all of them.

I think John is correct in his viewpoint.

[bjnockle]

JohnTopley: For De Nault, IV, Smithburg, this formula works (
=LEFT(A4,FIND("!",SUBSTITUTE(A4,",","!",LEN(A4)-LEN(SUBSTITUTE(A4,",",""))))-1)) for De Nault, IV, Smithburg, but still need to add it to the one you proposed to make it work. Are you able to take a look and see if it is doable? Thanks for your help with this.

[JohnTopley]

The simple answer is no because there isn't a way of determining when to apply the general formula I gave vs. the very specific one needed to solve the few anomalies [ other than testing for the specific names which defeats the objective:; other anomalies could arise in another sample]