RegEx not accepting pattern

mikegree

New Member
Joined
Apr 20, 2010
Messages
6
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,349
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,633
Office Version
  1. 365
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
\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

New Member
Joined
Apr 20, 2010
Messages
6

ADVERTISEMENT

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

mikegree

New Member
Joined
Apr 20, 2010
Messages
6
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,349
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,633
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,478
Messages
5,706,610
Members
421,460
Latest member
Taamrak

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top