Extract Text Between Multiple Parentheses

Question

Please help me with extracting Values between brackets and form an output String.

Sample: Aspirin (150mg) + Atorvastatin (10mg) + Clopidogrel (75mg) Capsule

Desired Output : 150mg + 10mg + 75mg

Thank You

Answer 1

You can try below formula but it is based on your example. Need to adjust Num_Chars parameter if data length in parentheses are different.

=MID(A1,SEARCH("(",A1)+1,5) & "+" & MID(A1,SEARCH("(",A1,SEARCH("(",A1)+1)+1,4) &"+"&MID(A1,SEARCH("(",A1,SEARCH("(",A1,SEARCH("(",A1)+1)+1)+1,4)

You can use below formula if data length is always 4 or 5 character in parentheses.

=SUBSTITUTE(MID(A1,SEARCH("(",A1)+1,5) & "+" & MID(A1,SEARCH("(",A1,SEARCH("(",A1)+1)+1,5) &"+"&MID(A1,SEARCH("(",A1,SEARCH("(",A1,SEARCH("(",A1)+1)+1)+1,5),")","")

Answer 2

Here's an alternative if you have access to TEXTJOIN formula. You need to enter it using Ctrl+Shift+Enter (convert it to an array):

=TEXTJOIN("mg + ",TRUE,IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"(",REPT(" ",255)),"mg)",REPT(" ",255)),1+255*ROW($A$1:INDEX($A:$A,LEN(A1))),255))*1,""))&"mg"

The result:

Answer 3

Another option to use TEXTJOIN function, and together with the INDEX+FILTERXML to force the array formula into a shorter and normal formula,

and the FILTERXML function return an array without error/blank cells, so the IFERROR() is not necessary

Then in B2, formula copied down :

=TEXTJOIN("+",1,INDEX(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,")",""),"(","<r/>")," ","</b><b>")&"</b></a>","//b[r]"),0))