Replace multiple characters in a string variable (VBA)

Question

How can I replace more than one thing in a string variable?

Here my example function in VBA:

Private Function ExampleFunc(ByVal unitNr$) As String
    If InStr(unitNr, "OE") > 0 Then
        unitNr = Replace(unitNr, "OE", "")
        unitNr = Replace(unitNr, ";", "")
    End If
...
End Function

Is there a better solution?

Answer 1

You could use an array and loop over its elements:

Sub MAIN()
    Dim s As String

    s = "123456qwerty"
    junk = Array("q", "w", "e", "r", "t", "y")

    For Each a In junk
        s = Replace(s, a, "")
    Next a

    MsgBox s
End Sub

Each element of junk can be either a sub-string or a single character.

Answer 2

If it is only about a few characters then I would go with Marco but in VBA:

unitNr = Replace(Replace(unitNr, "OE", ""), ";", "")

Answer 3

You can use regular expressions (AKA regex and regexp)!

While the regular expressions objects aren't enabled by default, there's a really comprehensive answer from Automate This that explains how to enable and use them at the following link:

https://stackoverflow.com/a/22542835/8213085

---

The function below is an example of how to use the RegExp object to replace a pattern, with a subroutine to illustrate how to use the function:

Private Function RegexReplace( _
    ByVal sInput As String, _
    ByVal sPattern As String, _
    Optional ByVal sReplace As String = "" _
)
    Dim regEx As New RegExp

    With regEx
        .Global = True
        .IgnoreCase = False
        .Pattern = sPattern
    End With

    If regEx.Test(sInput) Then
        Let RegexReplace = regEx.Replace(sInput, sReplace)
    Else
        Let RegexReplace = sInput
    End If

    Set regEx = Nothing
End Function


Private Sub Test()
    Debug.Print RegexReplace("a1b2c3", "[a-z]")  ' Prints 123
    Debug.Print RegexReplace("abc", "[a-z]")     ' Prints an empty string
    Debug.Print RegexReplace("123", "[a-z]")     ' Prints 123
    Debug.Print RegexReplace("abc", "a")         ' Prints bc
    Debug.Print RegexReplace("abc", "a", "b")    ' Prints bbc
End Sub

Since the answer linked above is so comprehensive, I won't explain how to construct patterns -- but I will note that this custom function can take 3 arguments:

• sInput: The string the search
• sPattern: The regex pattern to search for
• sReplace: An optional string to replace the matched string with. Defaults to an empty string ""

The pattern [a-z] used in the example subroutine replaces any lower-case letter between a and z, which is every lower case letter.

If you want to replace just the letters in qwerty like in Gary's Student's answer, you can just supply the pattern "[qwerty]":

Private Sub Test()
    Debug.Print RegexReplace("123456qwerty", "[qwerty]")  ' Prints 123456
End Sub

---

Regex is really powerful -- I'd definitely recommend trying to use it. Loads of modern software is starting to support it by default. If you're struggling to figure out what pattern to use, try testing it in the following webpage:

https://regexr.com/

Answer 4

I found this post quite helpful so I thought I would share how I was able to use it to my advantage. Combining the accepted answer from "Gary's Student" with this comment by Charles Williams, I came up with a slick way to remove non numeric characters from any given string.

Public Function RemoveNonNumChars(s As String) As String
    Dim bytes() As Byte
    Dim char As String

    bytes = StrConv(s, vbFromUnicode)
        For Each c In bytes
            char = chr(c)
            If Not IsNumeric(char) Then
                'remove character from array
                s = Replace(s, char, "")
                Debug.Print "Removing " & char & " from string." & Chr(13) s
            End If
        Next c
End Function

I hope this helps someone. Cheers!

Answer 5

I know this is a old thread, but this is what i was looking for, and it replaces multiple strings in a variable without having to use a Array or multiple lines

  Dim STARS As String
  STARS = "12ABC34"

  NewSTARS  = Replace(Replace(STARS, "A", ""), "B", "") 

  Output: 12C34

or every character you need to replace you just repeat the Replace function. For Example:

  Dim STARS As String
  STARS = "12ABC34"

  NewSTARS = Replace(Replace(Replace(STARS, "A", ""), "B", ""), "C", "")
  Output: 1234

The replace function works the exactly same way as a Forumla in excel, so if you have a formula already made, you can just copy and paste it into the VBA script.

Answer 6

Does it need to be VBA? This formula should also work:

=SUBSTITUTE(SUBSTITUTE("replace","e",""),"a","")

The output wil be 'rplc'