Extracting An Email Address From Text
This tip describes a VBA function that accepts a text string as input, and returns the first email address found in the text. The figure below shows this function in use. The formula in cell B2 is:
If an email address is not found, the function returns an empty string. Also, note that it only extracts the first email address.
The function is not very elegant. It just starts with the first "at symbol" it finds, and examines the characters before and after the at symbol.
Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String Const CharList As String = "[A-Za-z0-9._-]" 'Get location of the @ AtSignLocation = InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like CharList Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i If TempStr = "" Then Exit Function 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like CharList Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = _ Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks