Title: Replace all sub-strings in a string
Tags: excel;vba
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."
Comment: Would help to show an actual example.

Comment: 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

Here is the accepted answer: 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&gt; 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.
Comment for this answer: Sorry, I didn't pay attention it was excel VBA, my bad.can you check my update please?
Comment for this answer: @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.
Comment for this answer: update seems fine :-) + You might wish to include the appropriate lib reference as is early bound. Up to you.
Comment for this answer: @dahanraz It just replaced the first occurrence of the xyz(test)! Please fix this issue and could you explain more how the regEx works?
Comment for this answer: @dahanraz What are the beginning and ending parentheses used for?

Here is another answer: 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 &amp; " ", vbNullString)
I got the use of Filter() from this post

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2020:00:03/29/20', 'FavoriteCount': 0.0, 'PostTypeId': 1, 'Tags': '<excel><vba>', 'Title': 'Replace all sub-strings in a string', 'ViewCount': 145, 'id': 60911023, 'user_AboutMe': None, 'user_AccountId': 2246325.0, 'user_DisplayName': 'Fighter Jet', 'user_Reputation': 407.0, 'user_Views': 36.0, 'user_id': 1979578.0}}