How to remove any () and their contents from a string variable in Excel VBA without regex

I have a pre-defined function that looks up a string in a database, and returns an object. I need to pull a string from a worksheet, remove anything in parentheses(as well as the parentheses), and then pass it to this function. However, I can't get the Replace function to find more than just the parentheses themselves.

I tried the code:

  Set feederType = Cells(i, 5)
  feederCode = Replace(feederType, "(*)", "")
  feederID = getFeeder(feederCode).ID

Where i is a looping variable, feederType is a range, and getFeeder() takes a string and returns an object with the ID property. I have also tried this with feederType as a string, to the same result.

This only works for a direct substitution of "(*)", and does not actually replace anything in parentheses. Is there a way to do this replacement in a string variable without getting into regex?

EDIT: I have found a solution that works for my specific use case using InStr() and Left() since parentheses only appear at the end of my inputs. I am still curious, however, if any wildcard substitution functions already exist for string variables.

Assuming that there is only one set of parenthesis we can find the position of the opening and closing parentheses, get the substring, and remove it from the original string. I would use RegEx for more complex patterns.

Function RemoveParentheses(ByVal Text As String) As String
    Dim Result As String
    Dim OpeningParenPos As Long
    OpeningParenPos = InStr(Text, "(")
    
    Dim ClosingParenPos As Long
    ClosingParenPos = InStr(Text, ")")
    If OpeningParenPos > 0 And ClosingParenPos > 0 Then
        Result = Replace(Text, Mid(Text, OpeningParenPos, ClosingParenPos - OpeningParenPos + 1), "")
    Else
        Result = Text
    End If
    Result = Trim(Result)
    RemoveParentheses = Result
End Function

While I see no point in explicitly not using regular expressions, it can of course be done, but not with a one-liner (at least I cannot think of any).

The easy case is that you have only one pair of parentheses. Search for the leftmost opening and the rightmost closing parentheses.

Function removeParenthesesVersion1(s As String) As String
    Dim p1 As Long, p2 As Long
    p1 = InStr(s, "(")
    p2 = InStrRev(s, ")")
    If p1 > 0 And p2 > 0 Then
        removeParenthesesVersion1 = Left(s, p1 - 1) & Mid(s, p2 + 1)
    Else
        removeParenthesesVersion1 = s
    End If
End Function

However, if you have more than one pair of parentheses, you need to be a little bit more sophisticated. The following function loops over the string until no more pairs of parentheses can be found. In opposite to the first function, it looks for the leftmost closing parentheses.

Function removeParenthesesVersion2(ByVal s As String) As String
    Dim p1 As Long, p2 As Long
    Do While True
        p1 = InStr(s, "(")
        p2 = InStr(s, ")")
        If p1 > 0 And p2 > 0 and p2 > p1 Then
            removeParenthesesVersion2 = removeParenthesesVersion2 & Left(s, p1 - 1)
            s = Mid(s, p2 + 1)
        Else
            Exit Do
        End If
    Loop
    removeParenthesesVersion2 = removeParenthesesVersion2 & s
End Function

To test this:

Sub test()
    Const s1 = "Lorem(xyz)Ipsum"
    Const s2 = "Lorem(xyz)Ipsum(123)Dolor Sit"
    
    Debug.Print removeParenthesesVersion1(s1)
    Debug.Print removeParenthesesVersion1(s2)
    Debug.Print removeParenthesesVersion2(s1)
    Debug.Print removeParenthesesVersion2(s2)        
End Sub

results in

LoremIpsum
LoremDolor Sit
LoremIpsum
LoremIpsumDolor Sit

It gets even more tricky if you have nested loops and the like, but for this you first need to define what exactly you need as output before starting to code.

A little faster:

Function RemoveParenthContent(s As String) As String
   Dim openPar As Long, closePar As Long
   Const Lpar = "(", Rpar = ")"
   openPar = InStr(1, s, Lpar)
   If openPar > 0 Then
      closePar = InStr(openPar, s, Rpar)
      If closePar > openPar Then
         RemoveParenthContent = Left$(s, openPar - 1) & Mid$(s, closePar + 1)
         Exit Function
      End If
   End If
   RemoveParenthContent = s
End Function

'for any group of ()
Function RemoveParenthContent(s As String) As String
   Dim openPar As Long, closePar As Long
   Const Lpar = "(", Rpar = ")"
   Do
      openPar = InStr(1, s, Lpar)
      If openPar > 0 Then
         closePar = InStr(openPar, s, Rpar)
         If closePar > openPar Then
            s = Left$(s, openPar - 1) & Mid$(s, closePar + 1)
         Else
            Exit Do
         End If
      Else
         Exit Do
      End If
   Loop
   RemoveParenthContent = s
End Function

Even if the question has been answered, please also try the next function using arrays:

Function removeParanthesisAndContent(strP As String) As String
    Dim arr1, arr2, i As Long, strTxt As String

    arr1 = Split(strP, "(")
    If UBound(arr1) = 0 Then
        removeParanthesisAndContent = strP
    Else
        strTxt = arr1(0)
        For i = 1 To UBound(arr1)
            arr2 = Split(arr1(i), ")")
            strTxt = strTxt & arr2(1)
        Next i
        removeParanthesisAndContent = strTxt
    End If
End Function

It is able to process a string with as many parenthesis pairs as you want and it can be tested in the next way:

Sub ReplaceParanthesisAndInBetween()
    Const s = "Lorem Ipsum"
    Const s1 = "Lorem(abcd) Ipsum"
    Const s2 = "Lorem(abc) Ipsum(ssss) dolor sit"
    Const s3 = "Lorem (xyz)Ipsum(123) dolor sit(321) amet"
    Const s4 = "Lorem (xyz)Ipsum(123) dolor sit(321) amet(xyz)"
    
    Debug.Print removeParanthesisAndContent(s)
    Debug.Print removeParanthesisAndContent(s1)
    Debug.Print removeParanthesisAndContent(s2)
    Debug.Print removeParanthesisAndContent(s3)
    Debug.Print removeParanthesisAndContent(s4)
End Sub

Just to see the power of FilterXML, please test the next function. It should be very fast and compact (only a code line...):

Function removeParXML(txt As String) As String
    With Application
        removeParXML = Join(.Transpose(.FilterXML("<t><s>" & VBA.Replace(VBA.Replace(txt, "(", "</s><p>"), ")", "</p><s>") & "</s></t>", "//s[count(node())>0]")))
    End With
End Function

You can test it using:

Sub testRemoveParXML()
    Const strText = "Lorem (xyz)Ipsum(123) dolor sit(321) amet(qwerty)"
    
    Debug.Print removeParXML(strText)
End Sub

Not being possible to place eloquent comments to easily understand the function, I will place another one, split in smaller slices able to be commented and understood (I think):

Function removeParXMLDetails(txt As String) As String
    Dim strXML As String: strXML = "<t><s>" & VBA.Replace(VBA.Replace(txt, "(", "</s><p>"), ")", "</p><s>") & "</s></t>"
    
    Debug.Print strXML 'it creates a XML string having the main root tag <t></t> and nodes <s></s> for strings to REMAIN and
                       '<p></p> tag for paranthesis content (to NOT be extracted). See it in Immediate Window
                                       
    Dim arrXML: arrXML = Application.FilterXML(strXML, "//s[count(node())>0]") 'it returns a 2D (one column) array of all NOT EMPTY <s></> nodes!
                                                                                                                                         'If the string would not contain a last parenthesis pair,
                                                                                                                                         'returning an EMPTY NODE,  'XPath query string' (second parameter)
                                                                                                                                         'can only be "//s" to return ALL nodes (instead of "//s[count(node())>0]")...
    'In order to join the array contents it needs to be Transposed (to become 1D type):
    removeParXMLDetails = Join(Application.Transpose(arrXML))
End Function

In order to test it, you should only add at the end of the above testing sub the next code line:

   Debug.Print removeParXMLDetails(strText)

Many thanks to @T.M. who raised in me the interest and challenge of understanding the power of FilterXML, existing in Excel since 2013 version!