MS Excell: Extract email addresses from a excel sheet

Resources:

• http://www.yogeshguptaonline.com/2009/11/excel-macro-email-address-extract-from.html

In order to extract email address from excel cell you need VBA code in VBA Modules. Follow the steps:

• Enable developer ribbon: File-->Option-->Customized Ribon-->Check the Developer option
• Copy the following code into VBA Module: Select the workbook and press ALT+F11 or Developer-->Visual Basic-->Select the work sheet you are working on-->Right click-->Insert-->Module-->Copy the code:

Function Getmailid(cell As Range) As String

Dim Textstrng As String

Textstrng = cell.Text
Position@ = InStr(1, Textstrng, "@")
EmStart = InStrRev(Textstrng, " ", Position@)
If EmStart = 0 Then EmStart = 1
EmEnd = InStr(Position@, Textstrng, " ")
If EmEnd = 0 Then EmEnd = Len(Textstrng) + 1

mailid = Trim(Mid(Textstrng, EmStart, EmEnd - EmStart))

If Right(mailid, 1) = "." Then

• Extract the email: In the empty column, select a cell and write the formula =getmailid(B11). Here B1 is the cell number from where you need to extract email address.  Sample of the data and mail IDs extracted with the UDF is as below

• Updated formula: =IF((ISERROR(getmailid(A8))),"",getmailid(A8))

Sample:

Answer:

Array formula in D2:

=INDEX(A1:C1, 1, MIN(IF(ISERROR(SEARCH("@", A1:C1)), "", COLUMN(A1:C1))))

*SEARCH( substring, string, [start_position] )