I have a VBA RegEx function that searches for a ID with a pattern of 1 letter followed by 6 digits. I've been using the pattern [a-zA-Z]{1}\d{6} to find the IDs but am absolutely stuck on special cases.
Typically, the IDs are surrounded by non-character/numbers like "domain\J123456, Last login [etc]" so I tried [^a-zA-Z]{1}[a-zA-Z]{1}\d{6}[^a-zA-Z\d]{1} which kinda-worked. Where this pattern-string fails is
A further frustration is that VBA's implementation of RegEx is less than compliant and sometimes uses non-standard expressions. I know I could do multiple RegEx searches but that seems inefficient. Any help from The Gods would be greatly appreciated.
Specs: I'm using MS Access 2007 and the "Microsoft VBScript Regular Expressions 5.5" library (and my environment is locked down so I can't introduce new/different libraries into the mix).
EDIT: Here is some mocked up entries that present some of the scenarios. I'll bold what should be a match. There will only ever be 1 valid match per entry.
EDIT - The Solution
Based on the accepted answer below, here is the working function for anyone who needs it.
Public Function findID(search_str As String)
' Find [letter][6-digit number] ID in string
Dim regEx As New VBScript_RegExp_55.RegExp
Dim matches
findSID = ""
regEx.Pattern = "(?:^|[^a-zA-Z\d])([a-zA-Z]{1}\d{6})(?![a-zA-Z\d])"
regEx.Global = True
If regEx.test(search_str) Then
Set matches = regEx.Execute(search_str)
'-- Some of the matches return the match and left-most border-char. Trim it off
findID = Right(matches(0).Value, 7)
Else
findID = "No match"
End If
End Function
You use a regex of the form
(?:^|[^a-zA-Z\d])([a-zA-Z]{1}\d{6})(?![a-zA-Z\d])
The capture group 1 will contain the ID
(?![a-zA-Z\d]) asserts that the regex is not followed by digits or alphabets