# Extract Email Address from Text

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.

=ExtractEmailFromText(B1)

Type this formula into a blank cell and then press **Enter** key in your keyboard.

**#6** lets see the result: