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)
Limitations:
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
Pos@ = InStr(1, CellStrng, "@")
EmailStrt = InStrRev(CellStrng, " ", Pos@)
If EmailStrt = 0 Then
EmailStrt = 1
End If
emailend = InStr(Pos@, 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
HI
can you tell me know what is the code reuired if we need to extract many email id's that are inside a single cell.
I have copied the code above and pasted in to a module, saved, then cut and pasted =GetEmailID(B1)and I am getting extraction. What am I missing?