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:
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")
.Pattern = "[^a-zA-Z0-9]"
.IgnoreCase = True
.Global = True
Set inputMatches = .Execute(s)
If regEx.test(s) Then
removeInvisibleThings = .Replace(s, vbNullString)
removeInvisibleThings = s
Public Sub TestMe()
Debug.Print removeInvisibleThings("some spaces at the end!@# ")
Debug.Print removeInvisibleThings("Hategame.com is a web site that belongs to a friend!!!")
This would be the result at the immediate window: