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.

 

[Dunno123]

Peter_SSs: Thank you very much for your help, I appreciate it a lot. Your formula works like a charm!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Su_Yash]

Hello Peter_SSs,

This works like a charm for my case too :

=CONCAT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(J2,"]","["),"[",REPT("[",1000)),SEQUENCE(LEN(J2),,,2)*1000,1000),"[",""))

Could we have a way where i could insert a enter after each word ?

Ex:

hello [world] in this [good] human, [place] for [happy] [reason]

Expected is

world
good
place
happy
reason



Please help.

Thanks,

 

[Peter_SSs]

Could we have a way where i could insert a enter after each word ?

Welcome to the MrExcel board!

Sure, just use TEXTJOIN instead of CONCAT. Make sure that you format the result column to 'Wrap Text'

22 02 11.xlsm
	J	K
2	hello [world] in this [good] human, [place] for [happy] [reason]	world good place happy reason
TEXTJOIN v CONCAT

Cell Formulas
Range	Formula
K2	K2	=TEXTJOIN(CHAR(10),1,SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(J2,"]","["),"[",REPT("[",1000)),SEQUENCE(LEN(J2),,,2)*1000,1000),"[",""))

 

[Su_Yash]

Welcome to the MrExcel board!

Sure, just use TEXTJOIN instead of CONCAT. Make sure that you format the result column to 'Wrap Text'

22 02 11.xlsm
	J	K
2	hello [world] in this [good] human, [place] for [happy] [reason]	world good place happy reason
TEXTJOIN v CONCAT

Cell Formulas
Range	Formula
K2	K2	=TEXTJOIN(CHAR(10),1,SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(J2,"]","["),"[",REPT("[",1000)),SEQUENCE(LEN(J2),,,2)*1000,1000),"[",""))

It Worked very well . You just saved my 2 hours of time. Appreciate your help, i will follow this website . Thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.