Reputation: 13

Extract text from parentheses in Excel

I have an Excel list where I need text that exists within parenthesis. e.g.

From	Gene name	Column I need
O70257	syntaxin 7(Stx7)	Stx7
Q7TQ90	alcohol dehydrogenase 5 (class III), chi polypeptide(Adh5)	Adh5

I tried the following function, as suggested in a previous answer here:

=MID(text,SEARCH("(",text)+1,SEARCH(")",text)-SEARCH("(",text)-1)

It works for the first row, but for the second it only returns the first parenthesis content (class III). Is there a way to only take the text contained in the parentheses at the end?

Upvotes: 1

Answers (3)

JvdV

Reputation: 75870

In case one has access to the new functions (currently in Beta):

=TEXTBEFORE(TEXTAFTER(A1,"(",-1),")")

Or:

=TAKE(TEXTSPLIT(A1,{"(",")"},,1),,-1)

Or:

=TEXTAFTER(SUBSTITUTE(A1,")",""),"(",-1)

Would all work.

Upvotes: 2

Solar Mike

Reputation: 8375

So here is a different version:

No error checking and very simple,find is used twice to get the position of the second bracket . Mid uses find to get start and end.

Upvotes: 2

Mayukh Bhattacharya

Reputation: 27273

You may try this as shown in the image below,

Formula used in cell C2

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(B2,"(",REPT(" ",100)),100)),")","")

Another way,

=SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE($B2,"(","</s><s>")&"</s></t>","//s[last()]"),")","")

Upvotes: 3

Extract text from parentheses in Excel

Answers (3)

Related Questions