CODEWITHSUNDEEP
vbaexcelexcel-formula
tiborjan
tiborjan

Reputation: 31

Excel extract from brackets

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.

Upvotes: 2

Views: 1316

Answers (5)

brettdj
brettdj

Reputation: 55682

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

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

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

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 1

user3904868
user3904868

Reputation:

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

Upvotes: 1

ExcelArchitect.com
ExcelArchitect.com

Reputation: 41

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:

  1. s1: the string that contains the text you want to parse
  2. sLeft: the left-bound of the text you want to parse. Use "(" for your application.
  3. sL_Occ: iteration number of sLeft. So if you wanted the second "(" that appears in s1, make this 2.
  4. sRight: same as sLeft, but the right-bound.
  5. 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

Upvotes: 1

Related Questions