RegExp Object - Run-time error '5017' - No apparent pattern issues-CodePudding

I am using the RegExp Object to parse a common text field and turn it into a number. I have the pattern: (^[^0-9 -] )|((?<=[ -])[^0-9] )|((?<=[0-9])[^0-9.] ) which should turn an input like "37,080 lbs" into 37080.

For some reason, when I attempt to do this, I get Run-time error '5017'. I saw another post where the issue was caused by an error with the pattern, but I do not see any issues with my pattern and testing it out on a site like https://regex101.com/ shows that the pattern is valid.

Here is a sample script that illustrates this error:

Function Example()
    Dim Regex As Object
    Set Regex = CreateObject("VBScript.RegExp")
    With Regex
        .Global = True
        .MultiLine = False
        .Pattern = "(^[^0-9 -] )|((?<=[ -])[^0-9] )|((?<=[0-9])[^0-9.] )"
    End With
    
    Debug.Print Regex.Replace("37,080 lbs", "")
End Function

Note: I tagged VBScript because I am using the VBScript.RegExp Object. The code itself is VBA 6.5.1020 being run in Excel 2007.

CodePudding user response：

You can replace all occurrences of this pattern:

^[^0-9 -] |([ -])[^0-9] |([0-9])[^0-9.]

with

$1$2

See the regex demo.

Instead of lookbehinds ((?<=...)) that are not supported by VBA regex engine, you can use capturing groups, and restore the captured texts in the result with backreferences to the captured values.