VBA – RegEx in Excel – Part 2

After the first part about RegEx in Excel lately I had to do something else with RegEx.

The task was quite simple, pretty much called “stripping a string”. The idea is that you have a string and you should remove all the characters of it, which are not alphanumeric. As soon as you hear the task, it already seems like a question from CodeForces.com. I can even forecast the tests.

With RegEx in Excel, the important part is to pass the Pattern correctly (see regex101.com) and thus half of the job is done. The other half is to use late binding in order to avoid adding libraries and use correctly the RegEx.Execute(string) command.

The code looks like this:

Option Explicit

Public Function removeInvisibleThings(s As String) As String

    Dim regEx           As Object
    Dim inputMatches    As Object
    Dim regExString     As String

    Set regEx = CreateObject("VBScript.RegExp")

    With regEx
        .Pattern = "[^a-zA-Z0-9]"
        .IgnoreCase = True
        .Global = True

        Set inputMatches = .Execute(s)

        If regEx.test(s) Then
            removeInvisibleThings = .Replace(s, vbNullString)
        Else
            removeInvisibleThings = s
        End If

    End With

End Function

Public Sub TestMe()

    Debug.Print removeInvisibleThings("vitoshacademy.com")
    Debug.Print removeInvisibleThings("some spaces at the end!@# ")
    Debug.Print removeInvisibleThings("Hategame.com is a web site that belongs to a friend!!!")

End Sub

This would be the result at the immediate window:

vitoshacademycom
somespacesattheend
Hategamecomisawebsitethatbelongstoafriend

VBA – RegEx in Excel – Part 2

Related posts: