How to remove the LAST set Parentheses from a Excel text string that contains only numeric s

Question

I have an excel spreadsheet with over 50,000 entries. The entries have a name and address and sometimes a phone number ALL in the same string. I am concentrating on the phone number part of the string which is always at the end and enclosed in parentheses. I have been trying to use VBA code to address this. How to remove the LAST set Parentheses from a Excel text string that contains only numeric s between the parentheses. In any given string there may be either NO parentheses or multiple parentheses but I only want to remove that LAST set and leave the numbers contained there in the string

Example string "Toone Carkeet J., agt.,Alliance Assurance Co. Ltd. (Provident Life branch), 3 St. Andrew st. (1936)" I have tried using VBScript.RegExp to define "(1936)" but I cannot get the RegExp to match the string and replace the parentheses () with "".

For Each Cell In Range
    If strPattern<> "" Then
        strInput = Cell
        With regEx
            .Pattern="\(([0-9]))*)"
            .Global=False
        End With
        If .Pattern= True Then
            Replace(Cell.Value, "(","")
        End If

Answer 1

Here are two quick user defined functions that do not rely on regular expressions. The first uses VBA's StrReverse and the second InStrRev.

Function RemoveParens1(str As String)

    str = StrReverse(str)

    str = Replace(str, "(", vbNullString, 1, 1)
    str = Replace(str, ")", vbNullString, 1, 1)

    RemoveParens1 = StrReverse(str)

End Function

Function RemoveParens2(str As String)

    Dim o As Integer, c As Integer

    o = InStrRev(str, "(")
    c = InStrRev(str, ")")

    str = Left(str, c - 1) & Mid(str, c + 1)
    str = Left(str, o - 1) & Mid(str, o + 1)

    RemoveParens2 = str

End Function

If you don't want to use UDFs, just pick the logic method you prefer and adapt it for your own purposes.

Here's one more using regular expression's Replace.

Function RemoveParens3(str As String)

    Static rgx As Object, cmat As Object, tmp As String

    If rgx Is Nothing Then Set rgx = CreateObject("vbscript.regexp")

    With rgx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "\([0-9]*\)"

        If .test(str) Then
            Set cmat = .Execute(str)
            tmp = cmat.Item(cmat.Count - 1)
            tmp = Mid(tmp, 2, Len(tmp) - 2)
            str = .Replace(str, tmp)
        End If
    End With

    RemoveParens3 = str

End Function