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.
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.
Extract Email Address from Text with User Defined Function
You can also write a User Defined Function to extract email address quickly, just do the following steps:
#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module.
#4 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
#5 Type the following formula into blank cells and then press Enter key.
Type this formula into a blank cell and then press Enter key in your keyboard.
#6 lets see the result: