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.
Compare methods for extracting email addresses from text
All five methods can extract email addresses from text strings, but they differ considerably in setup, flexibility, and how they handle more complex data. Flash Fill is convenient for recognizable patterns, formulas work without macros but have limitations, and UDF or VBA solutions provide more control at the cost of code. Kutools for Excel provides a dedicated batch extraction tool without requiring formulas, pattern examples, or VBA.
| Method | Best for | Setup | Key consideration |
|---|---|---|---|
| Flash Fill | Quick extraction when Excel can recognize a consistent pattern | Manually enter an example, then press Ctrl + E | Fast and built in, but depends on Excel correctly recognizing the pattern |
| Kutools for Excel | Batch extracting email addresses from a selected range | Select the source range, choose Extract Email Address, specify the output location, and apply | No extraction formula, pattern example, or VBA code is required |
| Formula | Formula-based extraction without macros | Enter a long formula and fill it down the result column | Only extracts the first email in a cell; may return errors or include trailing punctuation |
| User Defined Function | Extracting multiple email addresses while returning results through a worksheet formula | Create the VBA function, enter =ExtractEmailFun(...), then fill it down | Can return multiple emails from one cell, but requires VBA code and a custom function |
| VBA | Code-based batch extraction and customization | Insert and run a VBA macro on the selected range | Can extract multiple emails, but the supplied macro overwrites the original 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
If you need to extract email addresses from many text strings, Kutools for Excel's Extract Email Address feature provides a dedicated batch solution. Instead of manually providing an example for Flash Fill, constructing a long extraction formula, or writing VBA code, you can select the source range, choose an output location, and extract the email addresses directly.
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 Kutools > Text > Extract Email Address.
- In the popping up Extract Email Address dialog box, select a cell to output the email addresses and click OK.
Result
The email addresses from the selected range are extracted to the specified output area immediately.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
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 the following 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 Function3. 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 the formulas above feel cumbersome, the following VBA code can help you quickly extract email addresses.
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 Sub3. 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 overwrite the original data, so it's recommended to back up your data first.
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in