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.