Extract the text from string between the last and the paired brackets

Question

I'm trying to extract the last group bounded by brackets from the string. The string could be like one of these variants:

String                             ' Extracted sub
--------------                     ' -------------
Some (text)                        ' text
Some text                          ' "" or "Some text"
Some (text) (here)                 ' here
Some (text) (is situated (here))   ' is situated (here)
Some text (is situated (here))     ' is situated (here)
Some (text) (is (situated (here))) ' is (situated (here))

I need the substring between the last closing bracket and the corresponding opening bracket.

All variants of Split, Mid, InStr and InStrRev I tested...

Answer 1

Not sure what have you tried, but the idea is (hope it's logical in writing):

1. Locate the location of last (right most) )
2. Locate the location of right most (
3. Calculate how many ) inside the substring from #2 to #1
4. Look at the characters before #2, increase #3 when ) is met, decrease #3 when ( is met, stop when #3 becomes zero.
5. The desired output is the substring from (#4+1) for (#1-1-#4) characters
6. If unmatched pairs found, it will return "ERROR: UNMATCHED BRACKETS!"

Code below tested in Excel (updated for sample YowE3K in comment)

Option Explicit

Function LastOutmostBracketText(ByVal InputText As String) As String
    Dim lRightMostCloseBracket As Long, CloseBracketCount As Long
    Dim lRightMostOpenBracket As Long
    Dim sTmp As String

    Dim sOutput As String
    If InStr(1, InputText, "(", vbTextCompare) > 0 And InStr(1, InputText, ")", vbTextCompare) > 0 Then
        ' Find the Last Close Bracket
        lRightMostCloseBracket = InStrRev(InputText, ")")
        ' Find the Last Open Bracket
        lRightMostOpenBracket = InStrRev(InputText, "(")
        If (lRightMostCloseBracket - lRightMostOpenBracket) > 1 Then
            ' Count how many Close brackets within the last Open and last Close bracket
            sTmp = Mid(InputText, lRightMostOpenBracket, lRightMostCloseBracket - lRightMostOpenBracket)
            CloseBracketCount = Len(sTmp) - Len(Replace(sTmp, ")", ""))
            ' Find the matching Open Bracket by looking at previous characters
            Do Until CloseBracketCount = 0 Or lRightMostOpenBracket = 1
                If lRightMostOpenBracket > 0 Then lRightMostOpenBracket = lRightMostOpenBracket - 1
                sTmp = Mid(InputText, lRightMostOpenBracket, 1)
                Select Case sTmp
                    Case "(":   CloseBracketCount = CloseBracketCount - 1
                    Case ")":   CloseBracketCount = CloseBracketCount + 1
                End Select
            Loop
            If lRightMostOpenBracket = 1 And CloseBracketCount > 0 Then
                sOutput = "ERROR: UNMATCHED BRACKETS!" & vbCrLf & InputText
            Else
                sOutput = Mid(InputText, lRightMostOpenBracket + 1, lRightMostCloseBracket - 1 - lRightMostOpenBracket)
            End If
        End If
    End If
    LastOutmostBracketText = sOutput
End Function

Answer 2

I suspect that regular expressions could indeed do this better but this is the codethat I knew how to write (now incorporating @YowE3K's "error case" and his more awake - despite his professed tiredness - understanding of how brackets within brackets should be treated):-

Private Function LastBracket(ByVal Txt As String) As String
    ' 08 Jan 2018

    Dim Fun As String
    Dim x As Integer, y As Integer
    Dim n As Integer, m As Integer

    For n = 0 To Len(Txt) - 1
        Fun = Fun & Mid(Txt, Len(Txt) - n, 1)
    Next n

    n = InStr(Fun, ")")                         ' remove trailing text
    If n Then
        Fun = Mid(Fun, n)
    Else
        Exit Function                           ' no bracket found
    End If

    Do
        n = InStr(m + 1, Fun, "(")
        If n Then
            Txt = Left(Fun, n)
            m = n
            x = Len(Txt) - Len(Replace(Txt, "(", ""))
            y = Len(Txt) - Len(Replace(Txt, ")", ""))
        Else
            Exit Function                       ' paired bracket not found
        End If
    Loop Until x = y

    Fun = Txt
    Txt = ""
    For n = 1 To Len(Fun) - 2
        Txt = Txt & Mid(Fun, Len(Fun) - n, 1)
    Next n

    LastBracket = Txt
End Function

It will return a null string if there is no bracketed text or the brackets are empty. Here are the tests I ran.

Private Sub TestUnpack()
    Debug.Print "Result = "; LastBracket("Some; Text")
    Debug.Print "Result = "; LastBracket("Some; Text()")
    Debug.Print "Result = "; LastBracket("Some(Text)")
    Debug.Print "Result = "; LastBracket("Some(Text)(here)")
    Debug.Print "Result = "; LastBracket("Some (text) (might be (here))")
    Debug.Print "Result = "; LastBracket("Some (text) (might be (situated (here)))")
    Debug.Print "Result = "; LastBracket("Some (text) (might be (situated (here))) not here")
    Debug.Print "Result = "; LastBracket("abc ((def) ghi (jkl) (mno (pqr)) stu) vwx")
End Sub

Answer 3

And a slightly different (but mostly the same) approach using CSet:

Sub test()
    Dim i As Integer
    Dim str As String
    Dim rng As Range
    Dim l As Integer
    For i = 1 To ActiveDocument.Sentences.Count
        Set rng = ActiveDocument.Paragraphs.Item(i).Range
        rng.End = rng.End - 1
        l = Len(rng.Text)
        rng.Collapse wdCollapseEnd
        Do
            rng.MoveStartUntil cset:="(", Count:=-l
            rng.Start = rng.Start - 1
            str = rng.Text
        Loop While Len(Replace(str, "(", vbNullString)) <> Len(Replace(str, ")", vbNullString))
        Debug.Print str
        str = vbNullString
    Next i
End Sub

Oh, I was too lazy to remove the outer brackets, but that should not be too problematic I hope ;-)