Extract text between MULTIPLE parentheses in one cell

[Dunno123]

Hello,

I would like to extract text between MULTIPLE parentheses in one cell (without using VBA).

The blue text in the picture below is what I'd like to achieve automatically.

When there is just one set of parentheses, it's easy. For B1 I could use:
=MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)

Is there any way I could change this formula to make it work with multiple parentheses as in cell A2?

Thank you in advance for your help.

 

[StuLux]

Is there a maximum of two sets of parentheses or could there be more than two?

 

[CA_Punit]

It might help you

Book1
	E	F	G	H
5
6	etract (text)		(text)
7	etract (text) (parenthesis) (PUNIT)		(text)(parenthesis)(PUNIT)
8
9
Sheet1

Cell Formulas
Range	Formula
G6:G7	G6	=TEXTJOIN("",TRUE,INDEX(TRIM(MID(SUBSTITUTE(E6," ",REPT(" ",LEN(E6))),(ROW(INDIRECT("A1:A"&LEN(E6)))-ROW(A1))*LEN(E6)+1,LEN(E6))),AGGREGATE(15,6,1/(1/(IFERROR(IF(CODE(TRIM(MID(SUBSTITUTE(E6," ",REPT(" ",LEN(E6))),(ROW(INDIRECT("A1:A"&LEN(E6)))-ROW(A1))*LEN(E6)+1,LEN(E6))))=40,1,FALSE)*ROW(INDIRECT("A1:A"&LEN(E6))),""))),ROW(INDIRECT("A1:A"&SUM(IFERROR(IF(CODE(TRIM(MID(SUBSTITUTE(E6," ",REPT(" ",LEN(E6))),(ROW(INDIRECT("A1:A"&LEN(E6)))-ROW(A1))*LEN(E6)+1,LEN(E6))))=40,1,FALSE),0)))))))

 

[CA_Punit]

considering you use office 2016, Textjoin function is not available in 2016. use Concat function if it is available in office 2016.

Please check

 

[Dunno123]

StuLux: There are sometimes more than two.
CA_Punit: Wow, that's a big code! Unfortunately it doesn't work for me. I tried to replace TEXTJOIN with CONCATENATE to no avail

 

[CA_Punit]

Not Concetanate , but CONCAT.
I doubt CONCAT is available in office 2016. I don't know how to join array values in single cell. Hope someone else could help you.

 

[Dunno123]

You are right, I don't have CONCAT there.

 

[Peter_SSs]

Welcome to the MrExcel board!

There are sometimes more than two.

What might be the maximum number?

Given your version of Excel and your requirement to not use vba (why?) this is not a simple task. We need as much detail as possible about what your actual data might look like. Could you share a few more (6-8) samples and the expected results with XL2BB so that we can easily use them to test with?

 

[Amit Tandon]

Presuming the 3 conditions (i) there will always be an opening and closing parentheses for a word, (ii) each word is separated with a blank space, (iii) within the opening and closing parentheses there will only be a single word / sub-string without space:

26Aug19.xlsx
	B
2	text
Sheet50

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(,,IF(LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))-1)*LEN(A2)+1,LEN(A2))),1)="(",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")," ",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))-1)*LEN(A2)+1,LEN(A2))),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

With Excel 2016 you will not be able to use the TEXTJOIN formula suggestions. If vba is disallowed, you could use a formula like this. You would need to keep adding terms, increasing the four 2s to 3s to 4s (as I have done so far) to 5s to 6s etc until you have enough terms in the formula to cover the maximum amount of sets of parentheses you might expect. It assumes that you do not have any nested parentheses.

20 07 04.xlsm
	A	B
1	extract (text)	text
2	extract (text) (parentheses) (text)	textparenthesestext
3	extract (text) other text here (parentheses and more) (text) and (more text) etc	textparentheses and moretextmore text
4	rwer
5
6	abc (def)	def
Extract Text

Cell Formulas
Range	Formula
B1:B6	B1	=REPLACE(LEFT(A1,FIND(")",A1&")")-1),1,FIND("(",A1&"("),"") &REPLACE(LEFT(A1,FIND("#",SUBSTITUTE(A1&REPT(")",2),")","#",2))-1),1,FIND("#",SUBSTITUTE(A1&REPT("(",2),"(","#",2)),"") &REPLACE(LEFT(A1,FIND("#",SUBSTITUTE(A1&REPT(")",3),")","#",3))-1),1,FIND("#",SUBSTITUTE(A1&REPT("(",3),"(","#",3)),"") &REPLACE(LEFT(A1,FIND("#",SUBSTITUTE(A1&REPT(")",4),")","#",4))-1),1,FIND("#",SUBSTITUTE(A1&REPT("(",4),"(","#",4)),"")

For other readers who may have the CONCAT function and the other new dynamic array functions like SEQUENCE you could try this - again assuming no nested parentheses.

20 07 04.xlsm
	A	B
1	extract (text)	text
2	extract (text) (parentheses) (text)	textparenthesestext
3	extract (text) other text here (parentheses and more) (text) and (more text) etc	textparentheses and moretextmore text
4	rwer
5
6	abc (def)
Extract Text (2)

Cell Formulas
Range	Formula
B1:B3	B1	=CONCAT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(A1,")","("),"(",REPT("(",1000)),SEQUENCE(LEN(A1),,,2)*1000,1000),"(",""))