extract text form text string( extract 5 charactors in front of all left parenthese)

Dear All,

I need to extract text form text string. there are multiple parentheses in the text string.
There are multiple parentheses in the text string.
My goal is to extract 5 charactors in front of all left parenthese.



First I tried

=TRIM(MID(SUBSTITUTE(SUBSTITUTE("("&$A2,"(",")"),")",REPT(" ",LEN($A2))),2*LEN($A2)*(COLUMNS($A:A)),LEN($A2)))

which works well to extract all the context inside the parentheses.

Then I tried

=MID(A2,SEARCH("(",A2)-5,SEARCH(")",A2)-SEARCH("(",A2)+7)

which works well to extract the 5 charactors in front of "(" but it only works for the first "(", not all of them in the test string.

I don't know how to combine these two to achieve my goal.

Please kindly help me out here..

With My Best Regards,

PS. If anyone could explain the first formula for me that would be great.
I am thinking if I could just edit a little based on the first formula to achieve my goal here?

Any help would be greatly appreciated.

Many thanks,

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

If you only want the 5 characters in front of the left (open) parenthesis try:

Formula:

Please Login or Register  to view this content.

If you want the 5 characters in front of the left (open) parenthesis and what is inside try:

Formula:

Please Login or Register  to view this content.

Note that which ever formula you wish to use should be pasted into cell B2 and then dragged over and down.
Let us know if you have any questions.