Skip to main content

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
1.


 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  
 Next  
2.
 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  
 Next  
 Tmpstr = Join(newVr, ",")  
 Tmpstr = IIf(Right(Tmpstr, 1) = ",", Left(Tmpstr, Len(Tmpstr) - 1),  
 Tmpstr)  
 ReplaceText = Tmpstr  
 End Function  
 FilterBetween = Join(NewText, Delimiter)  
 End Function  

Popular posts from this blog

Resolved : Power BI Report connection error during execution

Getting Below Power BI Report connection error during execution . Error: Something went wrong Unable to connect to the data source undefined. Please try again later or contact support. If you contact support, please provide these details. Underlying error code: -2147467259 Table: Business Sector. Underlying error message: AnalysisServices: A connection cannot be made. Ensure that the server is running. DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259 Microsoft.Data.Mashup.ValueError.DataSourceKind: AnalysisServices Microsoft.Data.Mashup.ValueError.DataSourcePath: 10.10.10.60;T_CustomerMaster_ST Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error Cluster URI: WABI-WEST-EUROPE-redirect.analysis.windows.net Activity ID: c72c4f12-8c27-475f-b576-a539dd81826a Request ID: dfb54166-c78f-4b40-779f-e8922a6687ad Time: 2019-09-26 10:03:29Z Solution: We found report connection not able to connect to SQL Analysis service so tried below option. Re

Song- Khamoshiyan Piano keyboard Chord,Notation and songs Lyrics

All songs notation and chords at one place

Song : O Saathi Re Film : Mukhathar Ka Sikkandhar Uses : C D D# E G A Note : The numbers at the end of the lines indicate line numbers. Pallavi: O saathi re, tere binaa bhi kya jina, tere binaa bhi kya jina A- C D D#....,D D C DD E...C..CA-...,D D C DD E...CC.......1 Play line 1 again phulon men khaliyon men sapnom ki galiyon men GGG...GAGE.. GGG G A G E.................................................2 tere bina kuchh kahin naa E A G E D C D D#.......................................................................3 tere binaa bhi kya jina, tere binaa bhi kya jina D D C DD E....C..CA-..., D D C DDE....CC.............................4 Charanam: har dhadkan men, pyaas hai teri, sanson men teri khushboo hai CCC C D C A-, CCC C D C A-, DDD DED CD EE.. CCCC......................5 is dharthi se, us ambar tak, meri nazar men tu hi tu hai CCC C D C A-, CCC C D C A-, DDD DED CD EE.. CCCC......................6 pyaar yeh tute naa GGG... GAG D#......E.........................