Extract Email Address from Text

This post will guide you how to extract email address from a text string in Excel. How do I use a formula to extract email address in Excel. How to extract email address from text string with VBA Macro in Excel.

Assuming that you have a list of data in range B1:B5 which contain text string and you want to extract all email addresses from those text string. How to achieve it. You can use a formula or VBA Macro to achieve the result. Let’s see the below introduction.

Extract Email Address from Text with a Formula


To extract email address from text string in cells, you can use a formula based on the TRIM function, the RIGHT function, the SUBSTITUTE function, the LEFT function, the FIND function, the REPT function and the LEN function. Just like this:

=TRIM(RIGHT(SUBSTITUTE(LEFT(B1,FIND(" ",B1&" ",FIND("@",B1))-1)," ",REPT(" ",LEN(B1))),LEN(B1)))

Select the adjacent Cell C1, and type this formula, and press Enter key in your keyboard, and then drag the AutoFill Handle over other cells to apply this formula.

exctract email address from text1

Extract Email Address from Text with User Defined Function


You can also write a User Defined Function to extract email address quickly, just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Function ExtractEmailFromText(s As String) As String
    Dim AtTheRateSignSymbol As Long
    Dim i As Long
    Dim TempStr As String
    Const CharList As String = "[A-Za-z0-9._-]"
 
    AtTheRateSignSymbol = InStr(s, "@")

    If AtTheRateSignSymbol = 0 Then
        ExtractEmailFromText = ""
    Else
        TempStr = ""
        For i = AtTheRateSignSymbol - 1 To 1 Step -1
            If Mid(s, i, 1) Like CharList Then
                TempStr = Mid(s, i, 1) & TempStr
            Else
                Exit For
            End If
        Next i
        If TempStr = "" Then Exit Function
        TempStr = TempStr & "@"
        For i = AtTheRateSignSymbol + 1 To Len(s)
            If Mid(s, i, 1) Like CharList Then
                TempStr = TempStr & Mid(s, i, 1)
            Else
                Exit For
            End If
        Next i
    End If
    If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1)
    ExtractEmailFromText = TempStr
End Function


#5 Type the following formula into blank cells and then press Enter key.

=ExtractEmailFromText(B1)

Type this formula into a blank cell and then press Enter key in your keyboard.

#6 lets see the result:

exctract email address from text3

 

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar