Vba – How to Replace Words With VBScript Regex and VBA

regexvbavbscript

what is vbscript syntax for .net:

\b[a-z]+\b

Hi

trying to replace all alpha words in the source.

.net regex tester confirms this pattern will find all words:

\b[a-z]+\b

this:

Findings,Actions

returns:

Findings

Actions

http://regexlib.com/RETester.aspx

But, in excel vba with vbscript object, it fails:

Sub test()
    Dim re As New VBScript_RegExp_55.RegExp
    re.Global = True
    re.Pattern = "\b[a-z]+\b"
    Debug.Print re.Replace("Findings, Actions", "xyz")
    Set re = Nothing
End Sub

' output is same as input– no replace happens

Best Answer

Sub test()

    Dim RE As Object
    Set RE = CreateObject("VBScript.RegExp")

    RE.ignoreCase = True
    RE.Global = True

    RE.Pattern = "\b[a-z]+\b"
    Debug.Print RE.Replace("Findings,Actions", "xyz")

End Sub

Output

xyz,xyz