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.
1. 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.
2. Extract Email Address from Text with User Defined Function
You can also write a User Defined Function with VBA Code to extract email address quickly, just do the following steps:
Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
Step2: then the “Visual Basic Editor” window will appear.
Step3: click “Insert” ->”Module” to create a new module.
Step4: 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
Step5: 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.
Step6: lets see the result:
3. Video: Extract Email Address from Text in Excel
This video will demonstrate a step-by-step instruction on how to use the formula and VBA code to extract email addresses from a block of text in Excel, making it easy to manage and organize your contact information.
