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
