How to Extract Email ID from a String in Cell
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 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
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P
AND GET A FREE!
E-BOOK FOR EXCEL VBA BEGINNERS
DON'T MISS ANY NEW ARTICLE !
Get your FREE! E-Book & Have Every New Article Delivered Straight To Your Email-Box