Wednesday, November 18, 2009

Excel Macro : Email Address Extract from String

Last week I got a mail from my friend asking for help in extracting email IDs from data available with him. The position of the mail IDs within the text string was not same and he was finding it difficult to us extract email addresses.

I wrote a quick UDF for him which did the job. I am sharing same with you as you may find it use full.

Sample of the data and mail IDs extracted with the UDF is as below



Here is the VBA code to Email Address Extract from String. You will need to copy this code to regular VBA module of your workbook

Function Getmailid(cell As Range) As String

Dim Textstrng As String

Textstrng = cell.Text
Position@ = InStr(1, Textstrng, "@")
EmStart = InStrRev(Textstrng, " ", Position@)
If EmStart = 0 Then EmStart = 1
EmEnd = InStr(Position@, Textstrng, " ")
If EmEnd = 0 Then EmEnd = Len(Textstrng) + 1

mailid = Trim(Mid(Textstrng, EmStart, EmEnd - EmStart))

If Right(mailid, 1) = "." Then
Getmailid = Left(mailid, Len(mailid) - 1)
Else
Getmailid = mailid
End If
End Function

Download file with VBA code to Email Address Extract from Text String
Posted by Yogesh Gupta at 5:05 PM
Labels: , , ,

25 comments:

  1. UnknownNovember 18, 2009 at 8:28 PM

    Did you change your code displayer? I don't remember this happening before, but when I copy/pasted your code into my copy of XL2003, everything was on a single line (that is, there were no line feeds between the individual statements).

    ReplyDelete
  2. UnknownNovember 18, 2009 at 8:34 PM

    Here is a worksheet formula that will extract an email address from the text in a cell...

    =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1)," ",REPT(" ",99)),99))&MID(A1,FIND("@",A1),FIND(" ",A1&" ",FIND("@",A1))-FIND("@",A1))

    In case your code displayer ends up breaking the line at blank spaces, I just want to point out that there are three blank spaces surrounded by quote marks in the formula... one on each side of the REPT function and one after the third FIND function.

    Rick Rothstein (MVP - Excel)

    ReplyDelete
  3. UnknownNovember 18, 2009 at 9:00 PM

    I just noticed that my formula has **four** blank spaces in it, not three and it looks like your display word wrapped it at the space I forgot to count. However, that should not matter since when I copy the formula from your webpage, it pastes correctly into Excel's Formula Bar as a single continuous line.

    ReplyDelete
  4. Yogesh GuptaNovember 18, 2009 at 11:09 PM

    Hello Rick

    I have not made any change in the code display. Could not understand why is it behaving like this. You can download the excel file having the code.


    Thanks for your formula input.

    Regards//Yogesh Gupta

    ReplyDelete
  5. UnknownNovember 19, 2009 at 2:06 AM

    Here is a macro that is a little more robust than the one you posted... instead of using just a blank space to delimit the email address from the rest of the text, it uses any character that is invalid in an email address to delimit the email address from the rest of the text. That way, you will be able to retrieve the email address if it is, for example, surrounded by parentheses, quote marks, etc. You will also be able to find the email address if it located at the end of a sentence (email addresses cannot start or end with a dot). Here is the code...

    Function GetEmailAddress(ByVal S As String) As String
    Dim X As Long, AtSign As Long
    Dim Locale As String, DomainPart As String
    Locale = "[A-Za-z0-9.!#$%&'*/=?^_`{|}~+-]"
    Domain = "[A-Za-z0-9._-]"
    AtSign = InStr(S, "@")
    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
    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

    Also note that I changed the argument's data type from Range to String... doing this still allows you to pass a cell reference into it when using it as a UDF, but it also allows you to pass it a simple text string as well (useful if you want to use the function in a non-UDF setting).

    Rick Rothstein (MVP - Excel)

    ReplyDelete
  6. Yogesh GuptaNovember 19, 2009 at 11:14 AM

    Hi Rick

    Thanks for your inputs. I tested the code and feel that there is some gap. After the first for next blok, "AtSign" variable needs to be recalculated by using statement
    AtSign = InStr(S, "@")

    It works fine after this change.

    I have updated the file at download link in the original post to include your code after this correction.

    Regards//Yogesh Gupta

    ReplyDelete
  7. UnknownNovember 19, 2009 at 2:20 PM

    Good catch! That is what I get for making last minute changes and not testing them. I'm glad spotted the problem and were able to figure out the solution. Thanks.

    Just a thought... as long as you decided to include my code in the download file, you might want to include a comment as to what the difference is between your code and mine, just in case someone reads your blog entry and downloads the file directly without reading the comments.

    Rick Rothstein (MVP - Excel)

    ReplyDelete
  8. Yogesh GuptaNovember 19, 2009 at 2:43 PM

    Though I had included credits to you in the download file but not mentined about the differnece between two.

    Now I have mentioned the difference and updated the file at download link.

    Now some thing beyond this post. I want to invite you for guest post on my blog. Any topic or any tips of your choice. Let me know if you are okay with this. I will be honored if you accept my invite.

    ReplyDelete
  9. UnknownAugust 4, 2010 at 5:45 PM

    Hi Yogesh,

    Your blog is awsome to excel learners.
    I have 300+ pdf files which i have to load into one excel sheet about its contents can u show me a formula or a vb code to create one excel file having details of all pdf files..

    Is there any way to do that
    Hope you understand its urgency!!!

    ReplyDelete
  10. Yogesh GuptaAugust 4, 2010 at 7:49 PM

    Hi Niku

    I am not clear about your question.

    If you want to read and update the content of the PDF files into Excel Sheet. Then I do not have solution for you.

    If you just want the list of the files to be updated then it can be done with the help of VBA.

    Pls revert back.

    Regards

    ReplyDelete
  11. UnknownAugust 6, 2010 at 12:16 PM

    Thanks for your reply

    I m clearing my question I have a file in which details of customers available eg.cust name mobile number etc.. one file for each customer and i have to make MIS of that pdf files we have generated online in one excel file.
    so any solution for that looking for early reply

    ReplyDelete
  12. Yogesh GuptaAugust 7, 2010 at 5:35 PM

    Hi Niku

    I do not have solution for this.

    Regards

    ReplyDelete
  13. AnonymousMarch 28, 2011 at 2:24 AM

    Nice thread! Thanks

    ReplyDelete
  14. saufiApril 18, 2011 at 7:27 PM

    I have a list of company websites that i would like to extract the email addresses. Anyone would be able to explain how this is done in Excel? Or of this is even possible?

    ReplyDelete
  15. VikasFebruary 9, 2012 at 2:52 PM

    Wonderful. Thanks a lot

    ReplyDelete
  16. AnonymousMarch 13, 2012 at 11:09 AM

    Hi,

    I have one excel sheet in shared folder and whenever that sheet is updated by any employee i should receive mail with the updated string. could u be please help me for the solution.

    Thanks,
    Jayesh

    ReplyDelete
  17. UnknownJuly 29, 2012 at 6:32 AM

    Hi Sir,
    The macro for finding emails from string helped us a lot. But we are able to find first occurence of emails alone. Can you help me in finding multiple emails in string?

    ReplyDelete
  18. Marie TullyNovember 7, 2012 at 8:23 PM

    I cannot get the VB to work. Could you kindly explain exactly how to open and run a VB? Ever so sorry. Actually, what i am trying to do overall is:

    1. In Excel extract 2000 email addresses from a single spreadsheet (parents who have subscribed by filling and signing a form) ;
    2. In Outlook, send a message to these addresses at the rate 100 per hour ;
    3. In Excel, remove the bounced addresses from the spreadsheet ;
    4. Do the same process from time to time from the same (but probably modified) spreadsheet.

    thanks in advance

    ReplyDelete
  19. S706November 21, 2012 at 1:51 AM

    For data where some strings may contain more than one email address, is there a way to alter Rick's function so that ALL address within the string are extracted?

    ReplyDelete
  20. samAugust 29, 2013 at 3:37 PM

    HI

    can you tell me know what is the code required or any thing i can do if we need to extract many email id’s that are inside a single cell.

    ReplyDelete
  21. UnknownOctober 26, 2013 at 8:07 PM

    I am new to this. I followed the instructions but now how do I run it to pull emails form the Excel file?

    ReplyDelete
  22. UnknownAugust 27, 2014 at 9:50 PM

    Here is another Free Excel tool that extract emails from a webpage or text. Also has Source code.
    http://officetricks.com/email-extractor-harvesting-email-addresses/

    ReplyDelete
  23. UnknownApril 3, 2016 at 12:00 AM

    Hi, I am wanting to include a button on my Excel that when activated automatically emails certain fields to an individual.

    ReplyDelete
  24. AnonymousMay 21, 2016 at 12:35 PM

    Hi Yogesh,

    I use the given vba coding and put in new work but its not working its asking new micro name.. would you like to suggest me what have to do first to get the result : chaudhary.rakesh23@gmail.com

    ReplyDelete
  25. AnonymousMay 21, 2016 at 12:35 PM

    please notify me

    ReplyDelete

Subscribe to: Post Comments (Atom)