I have this statement in excel
Blah blah blah blah blah(Pool 1 of 2) (science3262)
I want to extract the text "science3262".
I have tried this formula:
=MID(A5,FIND("(",A5) 1,FIND(")",A5)-FIND("(",A5)-1)
It is giving me output as "Pool 1 of 2"
CodePudding user response：
As per your added comment, I assume:
- The last set of paranthesis is always situated at the end of your string;
- There isn't always a 2nd set of paranthesis.
Therefor try:
Formula in B1:
=FILTERXML("<t><s>"&SUBSTITUTE(LEFT(A1,LEN(A1)-1),"(","</s><s>")&"</s></t>","//s[last()]")
CodePudding user response：
Try:
=MID(A1, FIND(")",A1) 3, FIND("(", A1, FIND("(",A1) 1) - FIND("(",A1)-3)
CodePudding user response：
Since you write that the last substring will always be at the very end, then you can use:
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",LEN(A1))),LEN(A1))),")","")