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

google.com/search?q=vba+regex+replace+site:stackoverflow.com in particular this one: stackoverflow.com/questions/22542834/… — Tim Williams
– Tim Williams, Commented Mar 29, 2020 at 6:55

T.M. · Accepted Answer · 2020-03-29 08:43:33Z

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, 2020 at 8:43

T.M.

10k3 gold badges40 silver badges61 bronze badges

answered Mar 29, 2020 at 7:15

dahan raz

4024 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dahan raz Over a year ago

Sorry, I didn't pay attention it was excel VBA, my bad.can you check my update please?

Fighter Jet Over a year ago

@dahanraz It just replaced the first occurrence of the xyz(test)! Please fix this issue and could you explain more how the regEx works?

dahan raz Over a year ago

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

Fighter Jet Over a year ago

@dahanraz What are the beginning and ending parentheses used for?

QHarr Over a year ago

update seems fine :-) + You might wish to include the appropriate lib reference as is early bound. Up to you.

HTH · Accepted Answer · 2020-03-30 11:56:40Z

0

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

answered Mar 30, 2020 at 11:56

HTH

2,0311 gold badge6 silver badges10 bronze badges

Collectives™ on Stack Overflow

Replace all sub-strings in a string

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related