Trim and Parentheses

[Coachmannyz8]

Good day,

I'm trying to use a formula to change the name and bring in the correct format into the appropriate cell, but I can't seem to find the right combination?

Example:
Data in cell B1 = Doe, John (John D. Doe)

my formula gives me = John Doe (John D. Doe) "=MID(G25&" "&G25,FIND(" ",G25)+0,LEN(G25))" or
Doe, John (John D. Doe) =TRIM(IF(ISERROR(OR(FIND("(",C3),FIND(")",C3))),C3,LEFT(C3,FIND("(",C3)-1)&MID(C3&" "&C3,FIND(" ",C3)+0,LEN(C3)))) or
Doe, John =SUBSTITUTE(C4,LEFT(MID(C4,FIND("(",C4),255),FIND(")",MID(C4,FIND("(",C4)+1,

My goal is to capture only = John Doe

Thanks in advance

 

[Scott T]

Try

=MID(A1,SEARCH(",",A1)+2,SEARCH("(",MID(A1,SEARCH(",",A1)+2,999))-1)&LEFT(A1,SEARCH(",",A1)-1)

 

[Coachmannyz8]

Thanks for the assistance Scott, I truly appreciate it.

One additional note:

How would you modify this formula to capture all, and any other possible combinations of text for example:
Security Inspector (No commas), and sometimes the same words but in ALL CAPS
Closeout
blank cells

I tried different versions of TRIM formulas, but I seem to be missing a letter, or no spacing between the words, or a blank cell, and a #VALUE !
=TRIM(IF(ISERROR(OR(FIND("(",C3),FIND(")",C3))),C3,LEFT(C3,FIND("(",C3)-1)&MID(C3&" "&C3,FIND(" ",C3)+0,LEN(C3))))

Thank You Scott.

 

[Scott T]

FIND is case sensitive. Search is not. If case does not matter use search.


Is something like this what you want?

=IF(AND(ISNUMBER(SEARCH("security inspector",A1)),ISNUMBER(SEARCH("closeout",A1))),"Security inspector, closeout",IF(ISNUMBER(SEARCH("security inspector",A1)),"Security inspector",IF(ISNUMBER(SEARCH("closeout",A1)),"closeout","")))