CODEWITHSUNDEEP
excelregexvba
Vishal Sharma
Vishal Sharma

Reputation: 172

Remove all special characters except a-zA-Z0-9 from a string

Is there a way to remove all special characters except a-zA-Z0-9 like in Python with:

((re.sub(r"[^a-zA-Z0-9]+", ' ', String)))

As we can use regex, below is the VBA function which can be used.

Public Function AllClean(TextToReplace As String) As String
    Dim ObjRegex As Object
    Set ObjRegex = CreateObject("vbscript.regexp")
    With ObjRegex
        .Global = True
        .Pattern = "[^a-zA-Z0-9_.\s]+"
        AllClean = .Replace(Replace(TextToReplace, "-", Chr(32)), vbNullString)
    End With
End Function

Upvotes: 1

Views: 4107

Answers (1)

PaichengWu
PaichengWu

Reputation: 2689

In VBA, late binding re with

Set re = CreateObject("vbscript.regexp")

for more information: https://msdn.microsoft.com/en-us/library/yab2dx62.aspx

update:

The function you want could be something like:

Function AllClean(str as string) as string
    With CreateObject("vbscript.regexp")
        .Global = True
        .ignorecase = True
        .MultiLine = True
        .Pattern = "[^a-zA-Z0-9]"
        AllClean = .replace(str, " ")
    End With
End Function

Upvotes: 5

Related Questions