Quickly extract email address from text string in Excel - A full guide

When dealing with imported data or mixed text strings in Excel, you might find yourself needing to extract email addresses quickly and accurately. Whether you’re working with a list of email addresses mixed with other text or need to pull emails from a large dataset, there are several efficient methods to get the job done. This guide will walk you through various techniques to extract email addresses from text strings, including using Flash Fill, Kutools for Excel, formulas, user-defined functions, and VBA code. Each method offers different advantages depending on your needs and the complexity of the data.

Extract email address from text string with Flash Fill

Excel's Flash Fill feature provides a quick and efficient way to automatically format or extract data based on patterns you define. In this section, we'll walk through how to use Flash Fill to extract email addresses from a column of mixed data. This method is particularly useful when dealing with lists containing various types of text and you only need to extract the email addresses.

1. Click on the first cell in the adjacent column (e.g., B2) where you want the email addresses to appear.
2. Manually type the email address from the corresponding row in Column A, and then press Enter.
   For example, if A2 contains "ana varela@gmail.com," type " ana varela@gmail.com " into B2.

   

3. Stay in the cell B3, press Ctrl + E to activate Flash Fill.
   Excel will automatically fill down the rest of Column B with the extracted email addresses.

   

One click to batch extract email address from text string with Kutools

When it comes to extracting email addresses from a dataset, Kutools for Excel’s Extract Email Address feature offers a powerful alternative to Excel's Flash Fill. While Flash Fill provides a manual approach to pattern recognition, Kutools for Excel automates the process with just one click, making it ideal for handling large volumes of data efficiently. This tool not only simplifies the extraction process but also ensures accuracy and speed, especially when dealing with complex or inconsistent datasets.

After downloading and installing Kutools for Excel, please do as follows:

1. Select the range of cells from which you want to extract email addresses. Then go to select Kutools > Text > Extract Email Address
2. In the popping up Extract Email Address dialog box, select a cell to output the email address and click OK

   

Result

Then all email addresses in each cell of the selected range are extracted immediately.

Extract email address from text string with Formula

Here I introduce you a long formula to extract only the email addresses from the text in Excel. Please do as follows:

1. In the adjacent cell B1, enter this formula =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",LEN(A1))),LEN(A1))).

2. Then press Enter key, then select the cell B1, and drag the fill handle to the range that you want to contain this formula. And the email addresses in the range have been extracted from the text string. See screenshot:

Notes:

1. The punctuation after the email address also will be extracted.

2. If the cells don’t contain the email addresses, the formula will display error values.

3. If there are more than one email address in a cell, the formula will only extract the first address.

Extract email address from text string with User Defined Function

Except the above formula, a User Defined Function also can help you get the email address from the text string.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module window.

Function ExtractEmailFun(extractStr As String) As String
'Update by extendoffice
Dim CharList As String
On Error Resume Next
CheckStr = "[A-Za-z0-9._-]"
OutStr = ""
Index = 1
Do While True
    Index1 = VBA.InStr(Index, extractStr, "@")
    getStr = ""
    If Index1 > 0 Then
        For p = Index1 - 1 To 1 Step -1
            If Mid(extractStr, p, 1) Like CheckStr Then
                getStr = Mid(extractStr, p, 1) & getStr
            Else
                Exit For
            End If
        Next
        getStr = getStr & "@"
        For p = Index1 + 1 To Len(extractStr)
            If Mid(extractStr, p, 1) Like CheckStr Then
                getStr = getStr & Mid(extractStr, p, 1)
            Else
                Exit For
            End If
        Next
        Index = Index1 + 1
        If OutStr = "" Then
            OutStr = getStr
        Else
            OutStr = OutStr & Chr(10) & getStr
        End If
    Else
        Exit Do
    End If
Loop
ExtractEmailFun = OutStr
End Function

3. Then save the code and enter the formula =ExtractEmailFun(A1) in an adjacent blank cell, see screenshot:

4. And then press Enter key, select the cell B1, and drag the fill handle over to the range that you need the formula. And all email addresses have been extracted from the cell text. See screenshot:

Notes:

1. If the cells don’t have the email addresses, it will reveal blank cells.

2. If there are more than one email address in a cell, all the emails will be extracted.

Extract email address from text string with VBA code

If you feel above formulas are troublesome for you, the following VBA code can help you extract the email addresses at one.

1. Hold down the ALT + F11 keys, and it opens a Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module window.

VBA: extract email addresses from text string

Sub ExtractEmail()
'Update 20130829
Dim WorkRng As Range
Dim arr As Variant
Dim CharList As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
arr = WorkRng.Value
CheckStr = "[A-Za-z0-9._-]"
For i = 1 To UBound(arr, 1)
    For j = 1 To UBound(arr, 2)
        extractStr = arr(i, j)
        outStr = ""
        Index = 1
        Do While True
            Index1 = VBA.InStr(Index, extractStr, "@")
            getStr = ""
            If Index1 > 0 Then
                For p = Index1 - 1 To 1 Step -1
                    If Mid(extractStr, p, 1) Like CheckStr Then
                        getStr = Mid(extractStr, p, 1) & getStr
                    Else
                        Exit For
                    End If
                Next
                getStr = getStr & "@"
                For p = Index1 + 1 To Len(extractStr)
                    If Mid(extractStr, p, 1) Like CheckStr Then
                        getStr = getStr & Mid(extractStr, p, 1)
                    Else
                        Exit For
                    End If
                Next
                Index = Index1 + 1
                If outStr = "" Then
                    outStr = getStr
                Else
                    outStr = outStr & Chr(10) & getStr
                End If
            Else
                Exit Do
            End If
        Loop
        arr(i, j) = outStr
    Next
Next
WorkRng.Value = arr
End Sub

3. Then Press F5 key to run this code, and you should select a range that you want to use the VBA in the popped out dialog, see screenshot:

4. Then click OK, and the email addresses have been extracted from the selected text strings. See screenshots:

Notes:

1. If the cells haven’t the email addresses, it will reveal blank cells.

2. All the emails will be extracted, if there are more than one email addresses in a cell.

3. The extracted emails will cover the original data, so you'd better backup the data first if you need.

Demo: Extract email address from text string with Kutools for Excel

Related article:

How to extract domains from multiple email addresses in Excel?