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?
CodePudding user response：
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()]"),")","")
CodePudding user response：
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.
CodePudding user response：
In case one has access to the new functions (currently in Beta):
=TEXTBEFORE(TEXTAFTER(A1,"(",-1),")")