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]

As a double check what does this return

=TEXTJOIN(" ",,FILTERXML("<k><m>"&SUBSTITUTE(A4," ","</m><m>")&"</m></k>","//m[3]"))

Also would you be happy with a UDF (macro) rather than a formula?

 

[pete1229]

As a double check what does this return

=TEXTJOIN(" ",,FILTERXML("<k><m>"&SUBSTITUTE(A4," ","</m><m>")&"</m></k>","//m[3]"))

Also would you be happy with a UDF (macro) rather than a formula?

The formula returns "Tomato".
I would be very happy with a macro if that makes it easier, thank you.

 

[Fluff]

Ok, how about

Function Pete(Txt As String) As String
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Split(Txt)
   For i = 0 To UBound(Ary)
      If InStr(1, Ary(i), "(") = 0 Then Pete = Pete & " " & Ary(i)
   Next i
   Pete = Trim(Pete)
End Function

used like

+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
5	Cucumber (1) Tomato Puree (2) Lettuce (3)	Cucumber Tomato Puree Lettuce
Master

Cell Formulas
Range	Formula
B2:B5	B2	=pete(A2)

 

[pete1229]

Absolutely perfect! Thank you very much for your help and patience!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Rick Rothstein]

If you don't mind fixing your data in place, you can use this non-looping macro to do it...

Sub pete1229()
  Application.ScreenUpdating = False
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Replace "(*)", "", xlPart, , , , False, False
    .Value = Evaluate("TRIM(" & .Address & ")")
  End With
  Application.ScreenUpdating = True
End Sub

If you need your fixed data in another column, then use this instead...

Sub pete1229()
  Application.ScreenUpdating = False
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Copy .Cells(1).Offset(, 1)
    With .Offset(, 1)
      .Replace "(*)", "", xlPart, , , , False, False
      .Value = Evaluate("TRIM(" & .Address & ")")
    End With
  End With
  Application.ScreenUpdating = True
End Sub