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

Extract Email Formula

Extract Email Formula

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)
         GetEmailID = eMailID
        End If
      End If
  End If

End Function


Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!