Assistance with Formulae - different scenarios

[shellp]

Hello

I have some text in a report that comes across a certain way, some with the same doctor formatted differently, and I want to create a formula to handle each scenario. I have 4 scenarios and I am having issues including all 4 scenarios in the same formula/column. Just to check that the syntax per condition was correct, I formatted 4 columns and added a different formula per column to ensure that each format was addressed and it was. But when I combine them altogether, it doesn't work. The final format should be Smith, Paul W for the scenarios below:

‘format where the word Dr. is included in the name
‘example Smith, Dr. Paul W
=IF(AND(ISERROR(FIND("(",M2,1)),FIND("DR.",M2,1)>0),TRIM(CONCATENATE(MID(M2,1,FIND(",",M2,1)-1),",",MID(M2,FIND("DR.",M2,1)+3,LEN(M2)))))

‘format where there is a bracket for the physician’s city but Dr. not also included in the name
‘example Smith, Paul W (Toronto)
=IF(AND(ISERROR(FIND("Dr.",M2,1)),FIND("(",M2,1)>0),TRIM(CONCATENATE(MID(M2,1,FIND(",",M2,1)-1),",",MID(M2,FIND(",",M2,1)+1,FIND("(",M2,1)-FIND(",",M2,1)-1))))

‘format where there is a bracket for the physician’s city AND Dr. also included in the name
‘example Smith, Dr. Paul W (Toronto)
=IF(AND(FIND("(",M2,1)>0,FIND("DR.",M2,1)>0),TRIM(CONCATENATE(MID(M2,1,FIND(",",M2,1)-1),",",MID(M2,FIND(".",M2,1)+1,FIND("(",M2,1)-FIND(".",M2,1)-1)))

‘format where there is no bracket and no Dr. included in the name
‘example Smith, Paul W
=IF(AND(ISERROR(FIND("(",M2,1)),ISERROR(FIND("DR.",M2,1))),M2)

The error I get when combining all is either #Value! for all cells or where the criteria worked in one of the 4 columns, it doesn't work when combined. Any and all assistance greatly appreciated, thanks.

 

[jasonb75]

I wouldn't even attempt to join them together, based on your examples, try

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A2,"Dr.",""),"(",REPT(" ",LEN(A2))),LEN(A2)))

 

[shellp]

Jasonb75, you are brilliant!! Thank you so much, this worked amazingly!!