Extract text outside multiple parenthesis

pete1229

New Member
Joined
Nov 6, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
I want to extract all text outside of any parenthesis. I can get the text from the first occurence of a parenthesis using:

=TRIM(LEFT(A2,FIND(" (",A2&" (")))

But now sure how to do this for multiple occurences. Below is an example.

TextExpected result
Cucumber (1)Cucumber
Cucumber (1) Tomato (2)Cucumber Tomato
Cucumber (1) Tomato (2) Lettuce (3)Cucumber Tomato Lettuce
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
How about
+Fluff 1.xlsm
AB
1TextExpected result
2Cucumber (1)Cucumber
3Cucumber (1) Tomato (2)Cucumber Tomato
4Cucumber (1) Tomato (2) Lettuce (3)Cucumber Tomato Lettuce
Master
Cell Formulas
RangeFormula
B2:B4B2=TEXTJOIN(" ",,FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[not(contains(.,'('))]"))
 
Upvote 0
How about
+Fluff 1.xlsm
AB
1TextExpected result
2Cucumber (1)Cucumber
3Cucumber (1) Tomato (2)Cucumber Tomato
4Cucumber (1) Tomato (2) Lettuce (3)Cucumber Tomato Lettuce
Master
Cell Formulas
RangeFormula
B2:B4B2=TEXTJOIN(" ",,FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[not(contains(.,'('))]"))
Thanks for your response. Unfortunately the result is always "Cucumber". I copied your solution directly so not sure why it's not working for me.

Example.png
 
Upvote 0
Did you copy/paste the entire mini-sheet using the copy icon?
 

Attachments

  • Copy icon.png
    Copy icon.png
    5 KB · Views: 14
Upvote 0
In that case I have no idea why it's doing that.
Try deleting the space before & after Tomato & then putting the space back in.
 
Upvote 0
Still doesn't work but I'll continue playing around with it. Perhaps it's a version issue...?
 
Upvote 0
It should work with 2019, two other things to try.
1) what happens if you put this in C4
Excel Formula:
=TEXTJOIN(" ",,FILTERXML("<k><m>"&SUBSTITUTE(A4," ","</m><m>")&"</m></k>","//m[2]"))
2) Click the Evaluate Formula button on the Formula tab & then step through the calculation by clicking the Evaluate button. After about 4/5 clicks you should see this
1619704403371.png


Do you?
 
Upvote 0
1) The value -1 is returned in cell C4 (using your exact formula).
2) Only "Cucumber" is returned inside the curly brackets.

Example_2.png
 
Upvote 0
The -1 is what I would expect, so it appears that with 2019 the filterxml function doesn't return an array.
Let me think on this.
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

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