May 17, 2013

Search a String for Words in VBA

Using VBA, How can I search a string for words surround by ( ) and remove them from the string.

There may be certain cases where I have a string equal to something like: "dog, cat, (pig), sheep, (elephant)". I need help creating a code that will search the string and remove the words surrounded in brackets resulting in: "dog, cat, sheep".

To make this a bit more challenging, in addition to removing words surrounded by ( ) I would like the comma's to adjust as well so I don't end up with: "dog. cat. . sheep, ," This would mean if sheep became the last word in the string it would no longer be followed by a comma.

This is a code suggestion for the same

 Function FilterBetween(Data As String, _  
 Optional Delimiter As String = ",", _  
 Optional OnLeft As String = "(", _  
 Optional OnRight As String = ")") _  
 As String  
 'remove anything in a delimited string which is surrounded by  
 ' a left and right value, e.g. '(' and ')' or 'start' and 'end'  
 Dim Text() As String  
 Dim NewText() As String  
 Dim i As Long  
 Dim Counter As Long  
 On Error Resume Next  
 'break data into an array  
 Text = Split(Data, Delimiter)  
 'check for left and right  
 For i = 0 To UBound(Text)  
 Text(i) = Trim(Text(i))  
 'check for left and right values  
 If Not (Left(Text(i), Len(OnLeft)) = OnLeft _  
 And Right(Text(i), Len(OnRight)) = OnRight) Then  
 Counter = UBound(NewText) + 1  
 ReDim Preserve NewText(Counter)  
 NewText(Counter) = Text(i)  
 End If  
 Public Function ReplaceText(str As String) As String  
 Dim Regx As New VBScript_RegExp_55.RegExp  
 Dim Vr As Variant  
 Dim newVr() As String  
 Dim ICounter As Long  
 Dim ArrCounter As Long  
 Dim Tmpstr As String  
 Regx.Pattern = "(\(.*\))"  
 Vr = Split(str, ",")  
 ArrCounter = 1  
 For ICounter = LBound(Vr) To UBound(Vr)  
 If Not Regx.Test(Vr(ICounter)) Then  
 ReDim Preserve newVr(ArrCounter)  
 newVr(ArrCounter - 1) = Vr(ICounter)  
 ArrCounter = ArrCounter + 1  
 End If  
 Tmpstr = Join(newVr, ",")  
 Tmpstr = IIf(Right(Tmpstr, 1) = ",", Left(Tmpstr, Len(Tmpstr) - 1),  
 ReplaceText = Tmpstr  
 End Function  
 FilterBetween = Join(NewText, Delimiter)  
 End Function