Extract all occurence between parenthesis

Maybe:

Option Explicit

Function fSplit(rSplit As Range)

' worksheet call: =fSplit(A1)
' VBA: Debug.Print fsplit(range("A1"))

Dim vArray, vElement, i As Long, lLen As Long

vArray = Split(rSplit, " ")
For i = LBound(vArray) To UBound(vArray)
    Debug.Print vElement; " before"
    vElement = Trim(vArray(i))
    If Left(vArray(i), 1) <> "(" Then
        vArray(i) = ""
    Else
        If Right(vArray(i), 1) = ")" Then lLen = 0
        If Right(vArray(i), 1) = "," Then lLen = 1
        vArray(i) = Mid(vArray(i), 1, Len(vArray(i)) - lLen)
    End If
    Debug.Print vArray(i); " after"
Next i

fSplit = Trim(Join(vArray, " "))

End Function

Regards, TMS

Well, that example does look a lot different to the previous example.

I suspect a different approach will be required.

Maybe tomorrow. Unless anyone else wants to pick it up?

Try out this UDF called ParenthesesText:

Option Explicit

Function ParenthesesText(ref As String)

Dim i As Long
Dim x As Integer
Dim OpenP As Long
Dim ClosedP As Long
Dim TotalP As Integer
Dim Txt As String

    ClosedP = 1
    
    For i = 1 To Len(ref)
        If Mid(ref, i, 1) = "(" Then TotalP = TotalP + 1
    Next i
        
    For x = 1 To TotalP
        OpenP = InStr(ClosedP, ref, "(")
        ClosedP = InStr(OpenP, ref, ")")
        Txt = Trim(Txt & " " & Mid(ref, OpenP, ClosedP - OpenP + 1))
    Next x
    
    ParenthesesText = Txt

End Function

Paste the code into a new module then use it like you would any other function by typing =ParenthesesText(ref) where "ref" is the reference to the cell containing your text.

For example, assume cell A1 contains the following text:
This (is) a (test).

To use the function, type =ParenthesesText(A1) into any cell.
The result should (is) (test)

Assumptions
There are no nested parentheses
There are no non-paired parentheses. In other words, there must be an equal number of open and closed parentheses

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.