'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?
Solution 1:[1]
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()]"),")","")
Solution 2:[2]
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.
Solution 3:[3]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | Solar Mike |
| Solution 3 |