Replace all sub-strings in a string

Question

In Excel VBA, how to replace all sub-strings of xyz(*)in a string which contains several instances of this sub-string? * in xyz(*) means every thing in between the two parenthesis. For example the string is "COVID-19 xyz(aaa) affects xyz(bbbbbb) so much families." This changes to "COVID-19 affects so much families."

https://www.google.com/search?q=vba+regex+replace+site:stackoverflow.com in particular this one: https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Tim Williams, Mar 29 '20 at 06:55

score 3 · Accepted Answer · edited Mar 29 '20 at 08:43

3

You should use a regular expression.
for example:

Sub a()

Dim Regex As New RegExp
Dim SubjectString As String
SubjectString = "COVID-19 xyz(test) affects xyz(test) so much, families."
With Regex
  .Global = True
  .Pattern = "(\sxyz(\S*))"
End With
Dim ResultString As String
ResultString = Regex.Replace(SubjectString, "")
MsgBox (ResultString)
End Sub

the first \s used to grab 1 whitespace before the xyz, so when you delete replace, it won't leave 2 white spaces. <br> then looking for the string xyz and the opening parenthesis, inside it I look for \S which is any char and * means 0 or more times and then I look for the closing parenthesis.

edited Mar 29 '20 at 08:43

T.M.

9,436
3
33
57

answered Mar 29 '20 at 07:15

dahan raz

372
2
9

1

Sorry, I didn't pay attention it was excel VBA, my bad.can you check my update please? – dahan raz Mar 29 '20 at 07:55
@dahanraz It just replaced the first occurrence of the xyz(test)! Please fix this issue and could you explain more how the regEx works? – Fighter Jet Mar 29 '20 at 08:24
1

@FighterJet fixed just add the flag .Global=True to the regex var. simple explanation to regex its some kind a pattern "language" that you can use to build your patterns and then find matches to the patterns. also make sure to mark my answer as correct if its what you asked for. – dahan raz Mar 29 '20 at 08:41
@dahanraz What are the beginning and ending parentheses used for? – Fighter Jet Mar 29 '20 at 10:10
update seems fine :-) + You might wish to include the appropriate lib reference as is early bound. Up to you. – QHarr Mar 29 '20 at 17:04

score 0 · Answer 2 · answered Mar 30 '20 at 11:56

here's a solution avoiding regexp, which I tend to avoid whenever possible and convenient (as this case seems to me)

Dim s As String
s = "COVID-19 xyz(aaa) affects xyz(bbbbbb) so much families."

Dim v As Variant
For Each v In Filter(Split(s, " "), "xyz(")
    s = Replace(s, v & " ", vbNullString)
Next

I got the use of Filter() from this post

Replace all sub-strings in a string

2 Answers2