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