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.
- Click on the first cell in the adjacent column (e.g., B2) where you want the email addresses to appear.
- 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.
- 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.
- Flash Fill is available in Excel 2013 and later versions. If you’re using Excel 2010 or earlier, this feature may not be available.
- Ensure that the cells where you’re applying Flash Fill are formatted as 'Text' to avoid unwanted formatting issues.
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:
- Select the range of cells from which you want to extract email addresses. Then go to select Kutools > Text > Extract Email Address
- 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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
