How to quickly extract email address from text string?
When you import some email addresses form Website to Excel worksheet, there always contains irrelevant text, but now you just want to extract the pure email addresses from the text string (see following screenshots). How could you quickly only get the email addresses from the cell text?
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 Multiple Email Addresses from text strings
Kutools for Excel’s Extract Email Address can help you to extract the email addresses from the text strings quickly and conveniently. Click to download Kutools for Excel!
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!
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.
Extract email address from text string with Kutools for Excel by one click
The above methods looks somewhat complicated for our Excel beginner, here, I can recommend you a quick and easy tool- Kutools for Excel, with its Extract Email Address utility, you can extract the email addresses from the text strings without much effort.
| Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
If you have installed Kutools for Excel, please do as follows:
1. Select the cells contain the text strings.
2. Click Kutools > Text > Extract Email Address, see screenshot:
3. And an Extract Email Address dialog box will pop out, select a cell where you want to put the result, see screenshot:
4. Then click OK button, all the email addresses have been extracted from the text strings, see screenshot:
Click to Download and free trial Kutools for Excel Now!
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!
