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.

Table of Contents

## 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.

## Leave a Reply

You must be logged in to post a comment.