RegEx not accepting pattern

[mikegree]

I am trying to use the code below to apply this regex pattern that I have validated against my data on regex101.com. In Excel I always get #VALUE! error. I have tried variations on the code but always get the same error. If I use different regex patterns the code works fine. Any idea what in this pattern is causing a problem?

\d\w *+[A-Za-z]\w\d\d?+ *+\d++


Code (just an example - the code works with other patterns)

Function RegxFunc(strInput As String, regexPattern As String) As String
Dim regEx As New RegExp
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = regexPattern
End With

If regEx.Test(strInput) Then
Set matches = regEx.Execute(strInput)
RegxFunc = matches(0).Value
Else
RegxFunc = "not matched"
End If
End Function

 

[Rick Rothstein]

I am not a RegExp person myself, but I bet it would help those who are if you showed some representative examples of your actual data and then told us what you think that RegExp pattern is looking for.

 

[Peter_SSs]

Any idea what in this pattern is causing a problem?

\d\w *+[A-Za-z]\w\d\d?+ *+\d++

Yes. In four places you have 2 quantifiers beside each other. In each of those four places, decide which quantifier you want (if any) & remove the unwanted one(s).

 

[sandy666]

\d\w *+[A-Za-z]\w\d\d?+ *+\d++
Quantifiers can only be used after a token that can be repeated
debug your regex on regex101.com if there is such a possibility

 

[mikegree]

Thank you! I‘ll give it a go and report back.

 

[mikegree]

Thank you all - updating the pattern to this solved my problem. The {2} was a slight improvement I learnt on the way.

\d\w\s*[A-Za-z]{2}\w[^"]\d\d?\s*\d+

I'm extracting data in the format 12AB12345 but there are random spaces in the source data and multiple strings per cell that I want to extract. That's why I resorted to regex.

 

[Rick Rothstein]

So your goal for this question is to give a text string to a function and see if it contains date in the form NNAANNNNN (where N is any digit and A is any letter)... if it does, then return that data value, otherwise return "Not Matched" from the function, is that correct? If so, what should the function return if there are two or more data values matching that pattern... what should the function return? Your attempted function appears to only return the first one. If that is what you want, you can also do it without using RegExp functionality.

Function GetNNAANNNNN(ByVal S As String) As String
  Dim X As Long
  S = " " & S & " "
  For X = 1 To Len(S) - 8
    If Mid(S, X, 11) Like "[!0-9]##[A-Za-z][A-Za-z]#####[!0-9]" Then
      GetNNAANNNNN = Mid(S, X + 1, 9)
      Exit Function
    End If
  Next
  GetNNAANNNNN = "Not Matched"
End Function

 

[Peter_SSs]

I don't see how that string given in post #6 could work in your function as is. For one thing, the double quote mark in it would need some special treatment.

If your aim was to extract 2 digits followed by 2 letters followed by 5 digits (with the possibility of some whitespace characters & other non-alphanumeric characters at certain points in it) like your sample in post 6 then even after fixing the quote issue, that pattern string will match other combinations of numbers/letters/other characters as well. It will certainly match many more combinations of characters than Rick's function (which is much more restrictive in relation to both whitespace characters & other non alphanumeric characters)

If your problem is solved that's fine but if there are any on-going issues it would be good to see 4 or 5 varied examples of what your cell data might look like as well as what the expected results and layout of those results. Also a description in words of exactly what you are trying to do in relation to those samples would help. Any sample data/expected results would be best with XL2BB so that we can easily copy and be sure we have exactly the same characters as you.