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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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:
|
1 2 3 |
vitoshacademycom somespacesattheend Hategamecomisawebsitethatbelongstoafriend |