1

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
Community
  • 1
  • 1
Vishal Sharma
  • 172
  • 4
  • 15
  • 1
    Possible duplicate of [Removing special characters VBA Excel](https://stackoverflow.com/questions/24356993/removing-special-characters-vba-excel) – Comintern Aug 01 '18 at 02:30

1 Answers1

5

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
PaichengWu
  • 2,649
  • 1
  • 14
  • 28
  • Thanks, for your help sir. That did the work for me. For beginners like me I have updated my question to provide additional info which can be used as a function. Thanks again... – Vishal Sharma Aug 01 '18 at 02:47