Get the value between the parentheses, multiple matches in one string

Question

My spreadsheet has a column with value like this string:

some text (text1) some test (text2) (text1)

How do I get all values between parentheses? The result I am looking for is:

text1, text2

Even if text1, text2... testn is present in the cell multiple times, I need it in the result only once.

I found a function GetParen here: Get the value between the brackets

It is helpful, but it gives the fist available value in the parentheses and ignores the rest.

To get multiple matches and not have duplicates, do you need the result in a single cell? If so this will require VBA. If you are ok with the results being in multiple cells, this can be done with formulas. — tigeravatar, Jan 12 '16 at 18:31

score 2 · Accepted Answer · 2016-01-13T02:45:22.657

It seems unwieldy to have one User Defined Function for individual entries and another for a collective result of all entries.

Paste the following into a standard module code sheet.

Function getBracketedText(str As String, _
                 Optional pos As Integer = 0, _
                 Optional delim As String = ", ", _
                 Optional dupes As Boolean = False)
    Dim tmp As String, txt As String, a As Long, b As Long, p As Long, arr() As Variant

    tmp = str
    ReDim arr(1 To 1)

    For b = 1 To (Len(tmp) - Len(Replace(tmp, Chr(40), vbNullString)))
        p = InStr(p + 1, tmp, Chr(40))
        txt = Trim(Mid(tmp, p + 1, InStr(p + 1, tmp, Chr(41)) - (p + 1)))
        If UBound(Filter(arr, txt, True)) < 0 Or dupes Then   '<~~ check for duplicates within the array
            a = a + 1
            ReDim Preserve arr(1 To a)
            arr(UBound(arr)) = txt
        End If
    Next b

    If CBool(pos) Then
        getBracketedText = arr(pos)
    Else
        getBracketedText = Join(arr, delim)
    End If
End Function

Use like any other native worksheet function. There are optional parameters to retrieve an individual element or a collection as well as changing the default <comma><space> delimiter.

Elbert Villarreal · Answer 2 · 2016-01-12T18:53:18.493

This code works for me:

Sub takingTheText()
Dim iniP 'first parenthesis
Dim endP 'last parentehis
Dim myText 'the text
Dim txtLen
Dim i
Dim tmp
Dim j
myText = Range("A1").Value

txtLen = Len(myText)
j = 0
Do 'Loop in the text
    i = i + 1 'a counter
    iniP = InStr(1, myText, "(", 1) 'found the first occurence of the (
    endP = InStr(1, myText, ")", 1) 'same as above

    tmp = tmp & Right(Left(myText, i), 1) 'take the text garbage text

    If i = iniP Then 'here comes the work
        j = j + 1 'here take the cell index
        myText = Replace(myText, tmp, "") 'remove the garbage text in front the first (
        tmp = Left(myText, endP - iniP - 1) 'reuse the var to store the usefull text
        Cells(1, 2).Value = Cells(1, 2).Value & Chr(10) & tmp 'store in the cell B1
        'If you want to stored in separated cells use the below code
        'Cells(j, 2).Value = tmp
        myText = Replace(myText, tmp & ")", "", 1, 1) ' remove the garbage text from the main text
        tmp = Empty 'empty the var
        i = 0 'reset the main counter
    End If
Loop While endP <> 0

End Sub

Result:

Please check and tellme if is ok.

Edit#1

Cells(1, 2).Value = Cells(1, 2).Value & Chr(10) & tmp this code store the text in separated lines inside the same cell, may be you want to use spaces between the resulting text because of chr(10) (also you can use chr(13)), then you can use Cells(1, 2).Value = Cells(1, 2).Value & " " & tmp, or use any other character instead the string inside the & symbols

ElbertV, please excuse my ignorance but i can't run the Sub you created. I created a module with the Sub, when i type in the cell "=takingTheText(K7)" it returns an error: "The name you entered is not valid". — dod, Jan 12 '16 at 19:39
You need to run the code inside a Module, this is not a function, thats why you get an error. Inside the code you can change the the cell with the text, or if is a range of cell, you can take them and create a loop (do, for) to go across the cell. — Elbert Villarreal, Jan 12 '16 at 19:46
May be, i can change the code to do a function for you... but i would need some time... — Elbert Villarreal, Jan 12 '16 at 19:46
Good procedure. I might have made it responsive to the current selection. See [my take on the subject](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/28700020?s=2|1.1545#28700020). — , Jan 13 '16 at 04:22

Get the value between the parentheses, multiple matches in one string

2 Answers2

Linked