Results 1 to 5 of 5
  1. #1
    Freefall is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    2

    Need to find records with pattern of digits in email address from exported file.

    I have file containing one million email addresses and need to query for only those that have pattern of digits to place into a separate table.


    The pattern would be as follows: 6 digits - 6 digits (ex. 860891-249271@myprovider.com)

    These records exist in addition to standard email addresses (ex. joe.johnson@myprovider.com) and need to have ability to only query for the specific digit pattern when needed.

    Using Microsoft Access 2013 on Windows 7 CPU. If you need additional information or clarification please let me know. First time on forum site so not sure what else may be needed?

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So what's the story on this, are ones with numbers those that bounce back and invalid or something? What is the email list about?

    I think Orange in another thread had some code that checked for a pattern like you are asking.

  3. #3
    Freefall is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    2
    I have a list of one million or so records which contain email address and maybe 700,000 are conventional (joe.johnson@myprovider.com) and other 300,000 are of the digit format. I want to be able to isolate the ones with digit format and then query further or use that as base (new table) for subsequent analysis. Email list just shows username and then some demographic information.

    I check for orange in other thread to determine if helpful to my particular question.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    If you know that only the invalid ones start with digits, i.e. that there are no valid address like 88John.Smith@...., then you could use the Val() function.

    val("860891-249271@myprovider.com") = 860891, so you could look for all addresses where val(address) > 0

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You can use regex --as per an older post I made.

    Here is a function that should identify records/emails that start with digits.
    Code:
    Function Emailfinder(t As String) As String
              Dim MyRE As Object
    10        Set MyRE = New Regexp
              Dim MyMatches As MatchCollection
              Dim MyResult As String
              'set the email pattern
    20        On Error GoTo Emailfinder_Error
    30        Emailfinder = ""  'set to empty string
    40      MyRE.Pattern = "^(\d+.*)" ' <<<---new pattern for leading digits 'MyRE.Pattern = "[-0-9a-zA-Z.+_]+@[-0-9a-zA-Z.+_]+\.[a-zA-Z]{2,4}"
    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
    160       On Error GoTo 0
    170       Exit Function
    
    Emailfinder_Error:
    
    180       MsgBox "Error " & err.number & " (" & err.Description & ") in procedure Emailfinder of Module Module1"
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Email report to value (email address) in a field
    By JackieEVSC in forum Programming
    Replies: 7
    Last Post: 08-28-2015, 11:18 AM
  2. Replies: 1
    Last Post: 05-01-2014, 11:37 AM
  3. How to find Country by using the email address?
    By jamesfranklin in forum Programming
    Replies: 2
    Last Post: 03-07-2013, 07:07 AM
  4. Replies: 13
    Last Post: 11-07-2012, 03:14 PM
  5. Replies: 1
    Last Post: 03-15-2012, 05:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums