If you want to extract email ID from a String Stored in any cell, it is possible by writing a Simple UDF (User Defined Function) in Excel. After writing this UDF, you can use this function as a normal Excel Function.
Follow the below Steps:
1. Open Visual Basic Editor (Alt+ F11)
2. Open an Existing Module or add a New Module
3. Copy Paste the below Code and Save the Excel.
4. Now Go to any Cell of any Sheet of your Workbook and use this formula =GetEmailID(Cell)
1. The below formula extract only one email ID which appears first in whole String
2. If many @ and . Signs are there then it may return wrong Value
Function GetEmailID(cell As Range) As String Dim CellStrng, eMailID As String Dim EmailStrt CellStrng = cell.Text If (InStr(1, CellStrng, "@") <> 0) Then [email protected] = InStr(1, CellStrng, "@") EmailStrt = InStrRev(CellStrng, " ", [email protected]) If EmailStrt = 0 Then EmailStrt = 1 End If emailend = InStr([email protected], CellStrng, " ") If emailend = 0 Then emailend = Len(CellStrng) + 1 End If eMailID = Trim(Mid(CellStrng, EmailStrt, emailend - EmailStrt)) If InStr(1, eMailID, ".") <> 0 Then If (Right(eMailID, 1)) = "." Then GetEmailID = Mid(eMailID, 1, Len(eMailID) - 1) Else GetEmailID = eMailID End If End If End If End Function