# Trim and Parentheses

#### Coachmannyz8

##### New Member
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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Scott T

##### Well-known Member
Try
Code:
``=MID(A1,SEARCH(",",A1)+2,SEARCH("(",MID(A1,SEARCH(",",A1)+2,999))-1)&LEFT(A1,SEARCH(",",A1)-1)``

#### Coachmannyz8

##### New Member
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

##### Well-known Member
FIND is case sensitive. Search is not. If case does not matter use search.

Is something like this what you want?
Code:
``=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","")))``

Replies
6
Views
78
Replies
6
Views
67
Replies
4
Views
69
Replies
2
Views
70
Replies
6
Views
66

Threads
1,109,533
Messages
5,529,397
Members
409,870
Latest member
Well59