Page 1 of 5 123 ... LastLast
Results 1 to 10 of 44

Thread: VBA To Extract Email Address From Text

  1. #1
    Junior Member
    Join Date
    Jun 2013
    Posts
    22
    Rep Power
    0

    VBA To Extract Email Address From Text

    Hey there. I have many Excel files which each have four columns of data (A, B, C, and D) and tens of thousands of rows. I need any email addresses present in column D extracted to column E. What's the best solution here? The text surrounding the emails is always different (ie the emails are not surrounded by <> or any other kind of constant characters that can simplify this).

    I've tried DigDB but it seems to have a run-time error 6 and/or 1004 issue no matter what I do. DigDB also misses more than a few emails that I can see in plan view. I've tried a couple other programs but they save the emails in a separate document, and I need the emails saved in the original file alongside the data it was extracted from, so that's no help to me.

    Any ideas? Thanks in advance.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=320960#p320960
    https://eileenslounge.com/viewtopic.php?p=320957#p3209573
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-04-2024 at 10:33 PM.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by dunndealpr View Post
    The text surrounding the emails is always different (ie the emails are not surrounded by <> or any other kind of constant characters that can simplify this).
    It has to be surrounded by something in order to be able to tell where it begins or ends... give us an idea of the type of characters you see on either side of it so we can get an idea how to proceed.


    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwplzlpYpmRqjGZem14AaABAg. 9hrvbYRwXvg9ht4b7z00X0
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgyOGlCElBSbfPIzerF4AaABAg. 9hrehNPPnBu9ht4us7TtPr
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwHjKXf3ELkU4u4j254AaABAg. 9hr503K8PDg9ht5mfLcgpR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw1-OyZiDDxCHM2Rmp4AaABAg.9hqzs_MlQu-9ht5xNvQueN
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg. 9htWqRrSIfP9i-fyT84gqd
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg. 9htWqRrSIfP9i-kIDl-3C9
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i57J9GEOUB
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i58MGeM8Lg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i59prk5atY
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwaWs6XDXdQybNb8tZ4AaABAg. 9i5yTldIQBn9i7NB1gjyBk
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxV9eNHvztLfFBGsvZ4AaABAg. 9i5jEuidRs99i7NUtNNy1v
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugx2zSXUtmLBSDoNWph4AaABAg. 9i3IA0y4fqp9i7NySrZamd
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9i7Qs8kxEqH
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9i7TqGQYqTz
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAJSNws8Zz
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAJvZ6kmlx
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAK0g1dU7i
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKCDqNmnF
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKHVSTGHy
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKSBKPcJ6
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKgL6lrcT
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKlts8hKZ
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKrX7UPP0
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAL5MSjWpA
    Last edited by DocAElstein; 07-09-2023 at 07:53 PM.

  3. #3
    Junior Member
    Join Date
    Jun 2013
    Posts
    22
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    It has to be surrounded by something in order to be able to tell where it begins or ends... give us an idea of the type of characters you see on either side of it so we can get an idea how to proceed.
    Hey Rick, thanks so much for your reply. Here's an example of the text in an average column D cell.

    "Thanks so much for contacting me you know you can always hit me at nadine_trotsky@yahoo.com or @nadine_trotsky on Twitter"


    That's pretty average. A lot of these people are people who direct messaged me over Twitter so their Twitter name is in the thread with another @ sign, which seems like it may confuse things further. Add to that, a lot of people I know don't exactly follow proper writing rules, so there might be a character pressed right up against the beginning or the end of their email address with no space. A lot of people end the sentence with their email address, meaning there's a period at the end of .com ("nadine_trotsky.com"). It's a bit of a minefield.

    Seems like the code should be set to look out for spaces and/or non-email characters, and I guess everything that doesn't catch we'll just have to chalk up as a loss..?

    Thanks again, Rick.



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-06-2023 at 04:14 PM.

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Give the following function a try. The first argument is the text string to parse and the optional second argument allows you to start the search from an arbitrary location within the text. If the function finds the first @ sign after the StartAt value (optional, defaulted to 1) and if that @ sign is not part of an email address, then the function returns the empty string. You will need to set up a loop that starts looking one character after each @ sign until it finds a valid email address.
    Code:
    Function GetEmailAddress(ByVal S As String, Optional StartAt As Long = 1) As String
      Dim X As Long, AtSign As Long
      Dim Locale As String, Domain As String
      S = Mid(S, StartAt)
      Locale = "[A-Za-z0-9.!#$%&'*/=?^_`{|}~+-]"
      Domain = "[A-Za-z0-9._-]"
      AtSign = InStr(S, "@")
      If AtSign < 2 Then Exit Function
      If Not Mid(S, AtSign - 1, 1) Like Locale Then Exit Function
      For X = AtSign To 1 Step -1
        If Not Mid(" " & S, X, 1) Like Locale Then
          S = Mid(S, X)
          If Left(S, 1) = "." Then S = Mid(S, 2)
          Exit For
        End If
      Next
      AtSign = InStr(S, "@")
      For X = AtSign + 1 To Len(S) + 1
        If Not Mid(S & " ", X, 1) Like Domain Then
          S = Left(S, X - 1)
          If Right(S, 1) = "." Then S = Left(S, Len(S) - 1)
          GetEmailAddress = S
          Exit For
        End If
      Next
    End Function

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    I'll use a Bazooka here (sorry Rick ), but here's my solution

    Code:
    Function ExtractEmail(strInputText As String) As String
    
        Dim regEx As Object
        Dim varResults As Object
        Dim varEach
        Dim lng As Long
        Set regEx = CreateObject("vbscript.RegExp")
        regEx.Pattern = "(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|""(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*"")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])"
        regEx.IgnoreCase = True 'True to ignore case
        regEx.Global = True 'True matches all occurances, False matches the first occurance
        If regEx.Test(Range("D1").Value) Then
            Set varResults = regEx.Execute(Range("D1").Value)
            For lng = 1 To varResults.Count
                ExtractEmail = ExtractEmail & varResults.Item(lng - 1).Value & "|||"
            Next
            ExtractEmail = Left(ExtractEmail, Len(ExtractEmail) - Len("|||"))
            ExtractEmail = Join(Split(ExtractEmail, "|||"), ", ")
        End If
        
    End Function
    Last edited by Excel Fox; 06-07-2013 at 04:04 PM. Reason: Minor Correction (lng - 1)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    By the way, the pattern I used was from here (not my invention)

    How to Find or Validate an Email Address
    Last edited by Rick Rothstein; 06-07-2013 at 12:50 PM. Reason: corrected the spelling of "pattern"
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    By the way, the pattern I used was from here (not my invention)

    How to Find or Validate an Email Address
    And [gulp] one hell of a pattern it is...

  8. #8
    Junior Member
    Join Date
    Jun 2013
    Posts
    22
    Rep Power
    0
    Hey all. Thanks so much for your time here. I saved the bazooka script as a macro and ran it, but I got a 'compile error: unexpected end sub' prompt. It seems when I created a macro called 'rickroth', Excel has added 'sub Rickroth' at the top of the code.


    Sorry if I'm making a silly mistake here. I'm adept enough at using VBA script in Word but for some reason it eludes me in Excel.
    Last edited by dunndealpr; 06-07-2013 at 01:45 PM.

  9. #9
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by dunndealpr View Post
    Hey all. Thanks so much for your time here. I saved the bazooka script as a macro and ran it, but I got a 'compile error: unexpected end sub' prompt. It seems when I created a macro called 'rickroth', Excel has added 'sub Rickroth' at the top of the code.
    I am not sure what you are saying here.... the code Excel Fox and I posted are not macros, they are functions meant to be called by other code (macros, event, etc.) or used as a function within a worksheet formula. Please explain exactly what you did and what happened when you did it.

  10. #10
    Junior Member
    Join Date
    Jun 2013
    Posts
    22
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    I am not sure what you are saying here.... the code Excel Fox and I posted are not macros, they are functions meant to be called by other code (macros, event, etc.) or used as a function within a worksheet formula. Please explain exactly what you did and what happened when you did it.
    currently browsing Excel 2007 For Dummies for a tutorial on how to insert this script properly. I know I should really know this kind of thing before coming to you guys, but if you can hit me with a link that explains how to do this, it would save me a lot of hair today. Sorry.

Similar Threads

  1. VBA Code to email using Globals Address Book
    By cdurfey in forum Excel Help
    Replies: 5
    Last Post: 05-28-2013, 10:25 PM
  2. Replies: 6
    Last Post: 05-25-2013, 07:36 PM
  3. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  4. Replies: 2
    Last Post: 03-21-2013, 08:51 PM
  5. Extract Email Text to Excel
    By bcloring in forum Excel Help
    Replies: 5
    Last Post: 12-14-2012, 04:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •