Regular Expression not working

[AlexanderBB]

Any idea what is wrong please?

Function NumbersOnly2(loc) As String

Dim RE As Object, REMatches As Object
Set RE = CreateObject("vbscript.regexp")

 RE.Pattern = "^[0-9]*$"
    If (RE.Test(loc) = True) Then
        Set REMatches = RE.Execute(loc)
         NumbersOnly2 = REMatches(0)
    End If
Set REMatches = Nothing
Set RE = Nothing
End Function

My test is
debug.print numbersonly2("J91f91")

This results in an empty string. By altering the pattern I can get some numbers returned, but not all.
Unsure too, if it should return a string, or a different data type. Or maybe it doesn't matter.

Thanks

 

[Rick Rothstein]

It doesn't use Regular Expressions, but I think this function will return the values you are looking for..

Function NumbersOnly(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X) = " "
  Next
  NumbersOnly = Replace(S, " ", "")
End Function

 

[pgc01]

Hi

My test is
debug.print numbersonly2("J91f91")

This results in an empty string. By altering the pattern I can get some numbers returned, but not all.

That's the expected result. Your code tests a string consisting of digits only.

 RE.Pattern = "^[0-9]*$"

The parameter you are using, "J91f91", is not digits only and so the result is the null string.

Unsure too, if it should return a string, or a different data type. Or maybe it doesn't matter.

You are defining the function as

Function NumbersOnly2(loc) As String

It returns a string.

P. S. BTW, since the regex expects a string as input I'd dimension loc as String.

 

[AlexanderBB]

Ah, interesting ! Thanks to you both. I had something very similar to Rick suggestion but
wanted to use a Regular expression - but maybe that's the wrong approach ?

The idea was to send in anything, and get back numbers only, if any.
I wanted my test to return "9191" which Ricks does, but do it without looping.

 

[Rick Rothstein]

Ah, interesting ! Thanks to you both. I had something very similar to Rick suggestion but
wanted to use a Regular expression - but maybe that's the wrong approach ?

The idea was to send in anything, and get back numbers only, if any.
I wanted my test to return "9191" which Ricks does, but do it without looping.

The way I constructed my code, the loop I used will be very fast... actually, given the time required to start the RegExp engine up, there is a good chance that my code will be faster than your function once you get the correct pattern to use.

 

[pgc01]

Hi

I agree with Rick. This is very simple and there's no need for a powerhouse like a regex.

If you are wanting to learn and master regex's, that's another thing. In that case anything, even small, is worth trying and testing.

This is how I'd do it.

Define the pattern as any character that is not a digit.
Set Global to true, so that the regex parses the whole string and does not stop after the first match.
Replace any character that matches with nothing.

Function NumbersOnly2(s As String) As String
Dim RE As Object

Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = "\D"
    .Global = True
    NumbersOnly2 = .Replace(s, "")
End With

Set RE = Nothing
End Function

 

[AlexanderBB]

I agree RegEx is a bit overkill but was curious why it wasn't working.
I had it wrong, I see that now! I'll go with Ricks method as I like it better than what I had (which used Select Case).
Thanks
ABB