Extract text from parentheses in Excel

user_13292393 0
Viewed 107

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?

excel-formula excel
3 Answers

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()]"),")","")

FORMULA_SOLUTION

user_81625200

So here is a different version:

enter image description here

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.

user_49617000

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.

user_97581940
Related