RegExp to Find String at END of a String

[Micron]

Access VBA: Anyone know the expression to find, for example (####) in "text (1234)" but not in "text (1234) text"?? I would like the match to be only where the digits found are 3 or more plus be at the end of the string, but I will settle for just getting the match at the end of the string. Getting desperate to move on.
I only know of Regular Expressions what I've tried in vain over the last 3 or so hours and I'm not getting the result I need. I have tried

.Pattern = "\([\w\s]*\)"
'.Pattern = "\([\w\s +$]\)"
'.Pattern = "\([\w\s+$]\)"
'.Pattern = "\([\w\s]+$\)"
'.Pattern = "\([\w\s$]\)"
'.Pattern = "\([\w\s]+$\)"
'.Pattern = "\([\w\s]$\)"
'.Pattern = "\([\w\s]\)$"
'.Pattern = "\([\d]\)$"

The only one that works at all is the first, which finds (1234) anywhere in the text. I would have thought .Pattern = "\([\d\d\d\d]*\) would limit itself to 4 digits, but it doesn't.

I thought this approach would be more elegant than trying to find the opening and closing parentheses at the end of a string, especially when I don't know if it will contain 3 or 4 digits. I want to set a boolean field (flag) if found and update a field to a proposed new string without the , and I have over 16000 records to search through. I know there are some here who know this stuff far better than me; hoping someone can rescue me. If not, I suppose I can InstrRev for "(" and ")" and get the Left of the string based on some number, but that's less elegant, and


Thanks.

[orange]

Micron,

Just saw this post. I don't know Regex all that well --I have used it on occasion but thought I'd try some vba.
Did this based on your post. Hope it's useful. If I have misunderstood the expected result and patterns, let me know.


A subroutine to look for pattern(s)
Pattern1 (####)
Pattern 2 (###)

'---------------------------------------------------------------------------------------
' Procedure : MicronRegEx
' Author    : mellon
' Date      : 17-Jan-2018
' Purpose   : To see if input string contain 1 of 2 possible patterns and
'identify the pattern matches.
'---------------------------------------------------------------------------------------
'
Sub MicronRegEx(s As String)
          
          Dim p1 As String
          Dim p2 As String
10       On Error GoTo MicronRegEx_Error

20        p1 = Right(s, 6)  '(####)
30        p2 = Right(s, 5)  '(###)

40        If p1 Like "(####)" Then
50            Debug.Print "found P1 in s " & p1 & "   " & s
60        ElseIf p2 Like "(###)" Then
70            Debug.Print "found P2 in s " & p2 & "   " & s
80        End If

        
MicronRegEx_Exit:
90       Exit Sub

MicronRegEx_Error:
100      MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure MicronRegEx of Module AWF_Related"
110      Resume MicronRegEx_Exit

End Sub

A test routine

'---------------------------------------------------------------------------------------
' Procedure : testMicron
' Author    : mellon
' Date      : 17-Jan-2018
' Purpose   :Test routine t exercise the MicronRegEx routine
'---------------------------------------------------------------------------------------
'
Sub testMicron()

          Dim strX(4) As String
10       On Error GoTo testMicron_Error

20        strX(0) = "123b7(56)abc)"
30        strX(1) = "12(3b7356)abc)"
40        strX(2) = "123b7(56)a(7895)"
50        strX(3) = "123b7(564)ab((784)"
60        strX(4) = "123b7(56)abc*(888888)"
          Dim i As Integer
70        For i = 0 To 4
80            MicronRegEx (strX(i))
90        Next i
        
testMicron_Exit:
100      Exit Sub

testMicron_Error:
110      MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure testMicron of Module AWF_Related"
120      Resume testMicron_Exit
End Sub

Update: After submitting the post I did find this to work with ~~minimal testing with Rubular
http://rubular.com/

[(]\d{3,4}[)]\z

Interpretation:
[(] ----the ( character
\d{3,4}--- followed by 3 or 4 digits
[)] -----followed by the ) character
\z ------at the end of the string


Note also: I have seen where \z was not accepted by some implementations. you may have to use a $
eg: [(]\d{3,4}[)]$

Another update:

I modified the test to use a vba function with regex, the $ was the issue with my regex attempts.

Here is the revised test using a function EmailFinder

'revised test
Sub testMicron()

          Dim strX(4) As String
          

10       On Error GoTo testMicron_Error

20        strX(0) = "123b7(56)abc)"
30        strX(1) = "12(3b7356)abc)"
40        strX(2) = "123b7(56)a(7895)"
50        strX(3) = "123b7(564)ab((784)"
60        strX(4) = "123b7(56)abc*(888888)"
          Dim i As Integer
      '80       For i = 0 To 4
      '90            MicronRegEx (strX(i))
      '100      Next i
70       For i = 0 To 4
80       Debug.Print strX(i) & "---" & Emailfinder(strX(i))
90       Next i

testMicron_Exit:
100      Exit Sub

testMicron_Error:
110      MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure testMicron of Module AWF_Related"
120      Resume testMicron_Exit
End Sub

Function Emailfinder(t As String) As String
    Dim MyRE As Object
    On Error GoTo Emailfinder_Error

10  Set MyRE = New regexp
    Dim MyMatches As MatchCollection
    Dim MyResult As String
    'set the email pattern
20
30  Emailfinder = ""  'set to empty string
     
40   MyRE.pattern = "[(]\d{3,4}[)]$"   ' <<<<<< I changed the pattern in this existing function for this test
50  MyRE.Global = True
60  MyRE.IgnoreCase = True
70  Set MyMatches = MyRE.Execute(t)
80  If MyMatches.Count > 0 Then
90      For Each mymatch In MyMatches
100         MyResult = MyResult & mymatch.value & vbCrLf
110     Next
120     Emailfinder = MyResult  ' one or more valid email addresses
130 Else
140     Emailfinder = ""    'empty string
150 End If


    On Error GoTo 0
    Exit Function

Emailfinder_Error:

    MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure Emailfinder of Module Module1"

End Function

Output from latest tests:

123b7(56)abc)---
12(3b7356)abc)---
123b7(56)a(7895)---(7895)

123b7(564)ab((784)---(784)

123b7(56)abc*(888888)---

[Micron]

Interesting. Nowhere did I see that /z was for looking at the end of the string (I tried to be careful to not look a C or Perl, etc. syntax). It seemed to be $, which I couldn't get to work. Maybe I had the wrong language information. I wasn't 100% positive that it was 3 or 4 numbers (could be 5), I ended up using

Sub MarkForDel()
Dim rs As DAO.Recordset
Dim firstPos As Integer, lastPos As Integer
Dim strNewPath As String

On Error GoTo errHandler
Set rs = CurrentDb.OpenRecordset("tblMarkForDelete")
If Not (rs.BOF And rs.EOF) Then
  rs.MoveFirst
    Do While Not rs.EOF
      If rs.Fields(2) <> True Then 'in case of subsequent attempts, this bypasses those already marked for deletion
         lastPos = Nz(InStrRev(rs.Fields(1), ")", -1), 0) 'if 0, there is no ending parenthesis...
         If lastPos <> 0 Then '...so skip
            firstPos = InStrRev(rs.Fields(1), "(", lastPos) 'find "(" in reverse; start from position of ")"
               If lastPos - (firstPos + 1) > 3 Then 'there are at least 3 digits when not including the "(" position
                 rs.Edit
                 rs.Fields(2) = True 'mark for visual check
                 rs.Update
               End If
         End If
      End If
      rs.MoveNext
  Loop
End If

exitHere:
Set rs = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
resume exitHere
    
End Sub

Most of the notation was added here, so it's possible that I've got a syntax error now. With the above, the digit count doesn't matter, albeit it won't distinguish between (1234) and (abcd). Fortunately my situation was that all were like (1234) or (12345) so no issue. One could test that each character is a number - or maybe just learn RegEx!

Anyway, I thank you for your response and effort. You have bailed me out before, as I've noted in this forum! Perhaps either of these code posts will help someone later on.

[orange]

Just saw your post. I have been editing mine for an hour. See my example using regex (Emailfinder is the name).

I am checking for (xxxx) or (xxx).
To check for 3 or 4 or 5 digits, at the end (with 3 to 5 digits)

try [(]\d{3,5}[)]$