# Regular Expression not working

#### AlexanderBB

##### Well-known Member
Any idea what is wrong please?

Code:
``````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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
It doesn't use Regular Expressions, but I think this function will return the values you are looking for..
Code:
``````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``````

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.
Code:
`` 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

Code:
``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.

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.

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.

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.

Code:
``````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``````

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

Replies
0
Views
102
Replies
4
Views
144
Replies
1
Views
128
Replies
22
Views
613
Replies
1
Views
153

1,203,642
Messages
6,056,500
Members
444,872
Latest member
Vishal Gupta

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back