Excel extract from brackets

Question

The code from Get the value between the brackets works well if the cell contains just one "(text)".

Unfortunately, in my rows there are many "Sample (sample1) (sample2)" format sentences and I need the last part.

Function GetParen(strIn As String) As String
Dim objRegex As Object
Dim objRegMC As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Pattern = "\((.+?)\)"
    If .Test(strIn) Then
        Set objRegMC = .Execute(strIn)
        GetParen = objRegMC(0).submatches(0)
    Else
        GetParen = "No match"
    End If
End With
Set objRegex = Nothing
End Function

Could somebody help me to modify the code? Because if the cell contains "Text (text part1) (text part2)" The result what I get is "text part1" but I need the "text part2". Thanks.

Gary's Student · Accepted Answer · 2015-03-07T13:33:28.917

1

Why bother with regex ?? Consider the alternative:

Public Function GetParen(strIn As String) As String
    Dim gather As Boolean, L As Long, i As Long
    Dim CH As String
    gather = False
    L = Len(strIn)
    For i = L To 1 Step -1
        CH = Mid(strIn, i, 1)
        If gather Then GetParen = CH & GetParen
        If CH = ")" Then gather = True
        If CH = "(" Then Exit For
    Next i
    GetParen = Mid(GetParen, 2)
End Function

enter image description here

EDIT#1:

Simpler:

Public Function GetParen2(strIn As String) As String
    ary = Split(strIn, "(")
    bry = Split(ary(UBound(ary)), ")")
    GetParen2 = bry(0)
End Function

edited Mar 07 '15 at 13:33

answered Mar 07 '15 at 13:14

Gary's Student

95,722
10
59
99

Upvoted your alternative, Regexp beats the character by character loop hands down though for even this simple match. – brettdj Mar 08 '15 at 02:31

score 1 · Answer 2 · answered Mar 07 '15 at 13:17

tiborjan:

I have a function that I wrote for just that reason. Here it is:

Function SubStr(s1 As String, sLeft As String, sL_Occ As Integer, sRight As String, sR_Occ As Integer) As String
'Cuts a piece of text from between two strings within another string
Dim LeftBound As Integer, RightBound As Integer, i As Integer

If sLeft = "Minimum" Then
    LeftBound = 0
Else
    For i = 1 To sL_Occ
        LeftBound = InStr(LeftBound + 1, s1, sLeft, vbTextCompare)
    Next i
End If

LeftBound = LeftBound + Len(sLeft) - 1  'measure from the right of the left bound

If sRight = "Maximum" Then
    RightBound = Len(s1) + 1
Else
    For i = 1 To sR_Occ
        RightBound = InStr(RightBound + 1, s1, sRight, vbTextCompare)
    Next i
End If

SubStr = Mid(s1, LeftBound + 1, RightBound - LeftBound - 1)

End Function

It has 5 parameters:

s1: the string that contains the text you want to parse
sLeft: the left-bound of the text you want to parse. Use "(" for your application.
sL_Occ: iteration number of sLeft. So if you wanted the second "(" that appears in s1, make this 2.
sRight: same as sLeft, but the right-bound.
sR_Occ: same as sL_Occ, but referring to the right-bound.

To make it easy: use this function code instead of yours. If you want to pull text from the second set of parentheses, use

s1 = "(P1) (P2) (P3) (P4)"

sLeft = "("

sL_Occ = 2

sRight = ")"

sR_Occ = 2

The return in the above would be "P2".

Hope that helps! Matt, via ExcelArchitect.com

score 1 · Answer 3 · answered Mar 07 '15 at 13:22

1

or how about simply

Function LastParam(ByVal str As String) As String
Dim arr() As String

    arr = Split(str, "(")
    LastParam = Split(arr(UBound(arr, 1)), ")")(0)
End Function

answered Mar 07 '15 at 13:22

score 0 · Answer 4 · answered Mar 08 '15 at 02:30

For completeness, you would only need minor changes to your code to make it work with your regex.

Set the Global flag to True, and return the last match from the match collection.

Function GetParen(strIn As String) As String
Dim objRegex As Object
Dim objRegMC As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Global = True
    .Pattern = "\((.+?)\)"
    If .Test(strIn) Then
        Set objRegMC = .Execute(strIn)
        GetParen = objRegMC(objRegMC.Count - 1).submatches(0)
    Else
        GetParen = "No match"
    End If
End With
Set objRegex = Nothing
End Function

score 0 · Answer 5 · answered Mar 08 '15 at 02:30

A minor tweak to my earlier Regexpwill extract the last match.

test

Sub Test()
MsgBox GetParen("(Sample (sample1) (sample2)")
End Sub

code

Function GetParen(strIn As String) As String
Dim objRegex As Object
Dim objRegMC As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Pattern = "\((.+?)\)"
    .Global = True
    If .Test(strIn) Then
        Set objRegMC = .Execute(strIn)
        GetParen = objRegMC(objRegMC.Count - 1).submatches(0)
    Else
        GetParen = "No match"
    End If
End With
Set objRegex = Nothing
End Function

Excel extract from brackets

5 Answers5