Extract text outside multiple parenthesis

[pete1229]

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.

Text	Expected result
Cucumber (1)	Cucumber
Cucumber (1) Tomato (2)	Cucumber Tomato
Cucumber (1) Tomato (2) Lettuce (3)	Cucumber Tomato Lettuce

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B
1	Text	Expected result
2	Cucumber (1)	Cucumber
3	Cucumber (1) Tomato (2)	Cucumber Tomato
4	Cucumber (1) Tomato (2) Lettuce (3)	Cucumber Tomato Lettuce
Master

Cell Formulas
Range	Formula
B2:B4	B2	=TEXTJOIN(" ",,FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[not(contains(.,'('))]"))

 

[pete1229]

How about

+Fluff 1.xlsm
	A	B
1	Text	Expected result
2	Cucumber (1)	Cucumber
3	Cucumber (1) Tomato (2)	Cucumber Tomato
4	Cucumber (1) Tomato (2) Lettuce (3)	Cucumber Tomato Lettuce
Master

Cell Formulas
Range	Formula
B2:B4	B2	=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.

 

[Fluff]

Did you copy/paste the entire mini-sheet using the copy icon?

 

[pete1229]

Yes.

 

[Fluff]

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.

 

[pete1229]

Still doesn't work but I'll continue playing around with it. Perhaps it's a version issue...?

 

[Fluff]

It should work with 2019, two other things to try.
1) what happens if you put this in C4

=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

Do you?

 

[pete1229]

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

 

[Fluff]

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.