Extract text between MULTIPLE parentheses in one cell

Dunno123

New Member
Joined
Jul 4, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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.

asd.png


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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Is there a maximum of two sets of parentheses or could there be more than two?
 
Upvote 0
It might help you

Book1
EFGH
5
6etract (text)(text)
7etract (text) (parenthesis) (PUNIT)(text)(parenthesis)(PUNIT)
8
9
Sheet1
Cell Formulas
RangeFormula
G6:G7G6=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)))))))
 
Upvote 0
considering you use office 2016, Textjoin function is not available in 2016. use Concat function if it is available in office 2016.

Please check
 
Upvote 0
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 :(
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
2text
Sheet50
Cell Formulas
RangeFormula
B2B2=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.
 
Upvote 0
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
AB
1extract (text)text
2extract (text) (parentheses) (text)textparenthesestext
3extract (text) other text here (parentheses and more) (text) and (more text) etctextparentheses and moretextmore text
4rwer 
5 
6abc (def)def
Extract Text
Cell Formulas
RangeFormula
B1:B6B1=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
AB
1extract (text)text
2extract (text) (parentheses) (text)textparenthesestext
3extract (text) other text here (parentheses and more) (text) and (more text) etctextparentheses and moretextmore text
4rwer
5
6abc (def)
Extract Text (2)
Cell Formulas
RangeFormula
B1:B3B1=CONCAT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(A1,")","("),"(",REPT("(",1000)),SEQUENCE(LEN(A1),,,2)*1000,1000),"(",""))
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,198
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top