How to replace multiple string at a time

Question

I am using following codes repeatedly. Is there any better alternative.

Dim strtxt as string
strtxt = Replace(strtxt, "String1", "")
strtxt = Replace(strtxt, "String2", "")
strtxt = Replace(strtxt, "String3", "")
strtxt = Replace(strtxt, "String4", "")
strtxt = Replace(strtxt, "String5", "")
strtxt = Replace(strtxt, "String6", "")
strtxt = Replace(strtxt, "String7", "")

Well you aren't as that code isn't legal as there is no `)`. Your way is clear and as good as any other way. — , Feb 24 '16 at 08:18
The aim is clear code that makes clear what it is doing. Your way is great. — , Feb 24 '16 at 09:30

score 2 · Accepted Answer · answered Feb 24 '16 at 08:27

2

Try this

Dim mLBound As Long
Dim mUBound As Long
Dim mSize As Long
Dim result As String
Dim RepChars As Variant

RepChars = Array("a", "b", "c")

mLBound = LBound(RepChars)
mUBound = UBound(RepChars)

result = Range("A2").Value

For mSize = mLBound To mUBound
    result = Replace(result, CStr(RepChars(mSize)), "")
Next

Range("A3").Value = result

answered Feb 24 '16 at 08:27

NJ.Lin

56
4

Better means less CPU Power and less lines. Any way This looks only solution right now. – Rahul Feb 24 '16 at 09:00

score 1 · Answer 2 · edited May 23 '17 at 12:07

Or the Regex could be used. Example based on this answer.

Option Explicit

Sub ReplaceWithRegex()
    Dim strPattern As String
    Dim strReplace As String
    Dim regEx As Variant
    Dim strtxt As String

    Set regEx = CreateObject("vbscript.regexp")
    strtxt = "String1.String2.String3.String4.String5.String6.String7.String77"
    strPattern = "(String.)" ' (String\d+) for replacing e.g. 'String77' etc.
    strReplace = ""

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If regEx.Test(strtxt) Then
        Debug.Print regEx.Replace(strtxt, strReplace)
    Else
        MsgBox ("Not matched")
    End If
End Sub

regexr

score 1 · Answer 3 · answered Feb 24 '16 at 11:08

1

One way:

Function RemoveTokens(target As String, ParamArray tokens() As Variant) As String
    Dim i As Long
    For i = 0 To UBound(tokens)
        target = Replace$(target, tokens(i), "")
    Next
    RemoveTokens = target
End Function

?RemoveTokens("AA BB CC DD EE", "BB")
AA  CC DD EE

?RemoveTokens("AA BB CC DD EE", "BB", "EE", "AA")
  CC DD

answered Feb 24 '16 at 11:08

Alex K.

171,639
30
264
288

I think there some mistake by SO. didn't receive notification of this answer. – Rahul Mar 17 '16 at 06:00

How to replace multiple string at a time

3 Answers3