ExcelInsider Team
Extracting email addresses from text can be a time-consuming task. Manually copying and pasting each email address is not only inefficient but also leads to errors. Fortunately, Excel offers several tools and functions that can help you automate this process, extracting emails quickly and accurately. In this article, we will walk you through several methods to extract email addresses from text in Excel, from simple built-in tools to more advanced functions and VBA macros.
To extract email from text in Excel, here is one simple solution by using the Flash Fill tool.
➤ Choose a corresponding cell next to the text and manually write down the email address from the corresponding row.
➤ Select the cell where you typed the email address and click Data > Data Tools > Flash Fill.
➤ Alternatively, you can use the keyboard shortcut Ctrl + E to get all the emails extracted from the text.
Using Excel Tools to Extract Email from Text
Excel’s built-in tools can help you extract emails, especially when your data has a consistent format. Imagine we have a sample dataset containing a list of names, email addresses, and contact numbers in a single column. In this section, we will use Excel’s Flash Fill tool, Text to Column feature, and Power Query to extract email from text.
Flash Fill
Flash Fill is a tool that automatically recognizes a pattern in your data and fills the rest of the column based on that pattern. This is often the easiest method if your data is well-structured.
➤ First, add a new column next to your data and name it “Email Address”.
➤ In the first cell of the new column (B2), manually type the email address from the corresponding row.
➤ Now, select the cell where you typed the email address (B2).
➤ Go to the Data tab on the ribbon and click on Flash Fill from the Data Tools group.
Flash Fill will instantly recognize the pattern and fill the rest of the column with the email addresses. This method works properly if your data has a consistent format.
Text to Columns Feature
The Text to Columns feature is another option when the email addresses are separated by a consistent character, such as a comma, space, or semicolon. For this method, we will use the same dataset as the previous example.
➤ Select the cells in the column that contain the email addresses you want to extract.
➤ Go to the Data tab and click on Text to Columns from the Data Tools group.
The Convert Text to Columns Wizard will appear.
➤ In Step 1, choose Delimited as your original data type, and click Next.
➤ In Step 2, check the box Comma under Delimiters and click Next.
The data preview will show how your data will be separated into different columns.
In Step 3, you can choose to skip the columns that you don’t need.
➤ Select the first column (“Name”) and the third column (“Contact Number”) and choose Do not import column (skip).
➤ Set the Destination to a new column where you want the emails to appear (e.g., $B$2).
➤ Click Finish.
Finally, Excel will populate the new column with only the email addresses, as shown below.
Power Query Tool
For more complex data where emails are embedded within various text strings, Power Query can provide a simple solution. This method is especially useful for handling a large amount of data without needing to write complex formulas.
Let’s use a dataset where email addresses are mixed with other text.
First, convert your data range into a table.
➤ Select the data and press Ctrl + T .
➤ In the “Create Table” dialog box, ensure the correct range is selected and checkmark My table has headers.
➤ Click OK.
Following the steps, the data will be in a table format.
➤ With your table selected, go to the Data tab.
➤ In the “Get & Transform Data” group, click From Table/Range.
This will open the Power Query Editor.
➤ In the Power Query Editor, select the column with your text string.
➤ Go to the Transform tab, click Split Column, and select By Delimiter.
➤ In the “Split Column by Delimiter” dialog box, select Space from the “Select or enter delimiter” dropdown.
➤ Under “Split into“, select Rows.
➤ Click OK.
Power Query will transform the data, putting each word from the original text into a new row. Now, we will filter this new column to keep only the rows that contain an “@” symbol.
➤ Click the filter arrow next to the “Text String” header.
➤ Go to Text Filters and select Contains.
➤ In the “Filter Rows” dialog, type @ in the text box and click OK.
The table will now show only the rows that contain an email address.
➤ To bring this data back to your Excel sheet, go to the File tab and select Close & Load To.
➤ In the “Import Data” dialog box, choose Existing worksheet and select the cell where you want to place the data (e.g., $B$1) and click OK.
As a result, the extracted emails will be loaded into your worksheet.
Applying Excel Functions to Extract Email from Text
Excel functions can provide you with a dynamic solution to extract email from text, even when your data source changes. Here, we will use several functions to extract email from a text string.
TRIM, MID, SUBSTITUTE, REPT, and FIND Functions
With the combination of TRIM, MID, SUBSTITUTE, and FIND functions, you can extract a substring between two known characters. In our case, we will look for a space before and after the “@” symbol and extract it, which is our email address.
➤ In a new cell (B2 in our example), enter the following formula and press ENTER.
=TRIM(MID(SUBSTITUTE(A2," ","",REPT(" ",25)),FIND("@",SUBSTITUTE(A2," ","",REPT(" ",25)))-25,50))
➤ Drag the formula down to apply it to all the cells in your data.
➧ SUBSTITUTE(A2," ","",REPT(" ",25)): Replaces every single space in the text string with 25 spaces. This ensures there are always enough spaces around the email to work with.
➧ FIND("@",...): Finds the position of the "@" symbol.
➧ MID(...,FIND(...)-25,50): Extracts a substring of 50 characters, starting 25 characters before the "@" symbol. This guarantees the email will be included.
➧ TRIM(...): Removes all the extra spaces, leaving only the email address.
Finally, we have successfully extracted email from text in Excel.
FILTERXML and SUBSTITUTE Functions
Here, we will use the combination of the FILTERXML and SUBSTITUTE functions to extract email from text. The FILTERXML function treats the text string as an XML data structure, and the SUBSTITUTE function replaces every space with the XML tags. Finally, returns all the elements that contain the “@” symbol. This formula works if you have the latest version of Excel (2013 and later for Windows).
➤ In a new cell (B2), enter the following formula and hit ENTER.
=FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s[contains(.,'@')]")
➤ Drag the formula down to fill the rest of the column.
Thus, you will get the email extracted in a new column.
Extracting Multiple Emails from a Single Text String
Sometimes, a single cell might contain more than one email address. In that case, you can use a combination of TEXTJOIN, FILTER, and TEXTSPLIT functions if you have Excel 365.
Consider a dataset with multiple emails in one cell.
➤ In a new cell (B2), enter the following formula and click ENTER.
=TEXTJOIN(",",TRUE,FILTER(TEXTSPLIT(A2,","),ISNUMBER(SEARCH("@",TEXTSPLIT(A2,", ")))))
➤ Drag the Fill Handle down to fill the rest of the cells.
Thus, the formula will return all emails from the cell.
Combining Emails into a Single Cell from Multiple Columns
Sometimes, email addresses are scattered across multiple columns within your dataset. You can combine the text from these columns and then extract the emails into a single cell. Here, we will use the combination of TEXTJOIN, FILTER, TEXTSPLIT, ISNUMBER, and SEARCH functions to extract email from multiple columns into a single cell separated by a comma.
Imagine a dataset where we have email addresses in different columns.
➤ In a new cell (C2), enter the following formula and press ENTER.
=TEXTJOIN(",",TRUE,FILTER(TEXTSPLIT(TEXTJOIN(" ",TRUE,A2:B11)," "),ISNUMBER(SEARCH("@",TEXTSPLIT(TEXTJOIN(" ",TRUE,A2:B11)," ")))))
➤ Drag the formula down to fill all the cells.
The result will be a single cell containing all the extracted emails from both columns.
Embedding VBA Code to Extract Email from Text
You can use a VBA macro to find and extract all email addresses with a single click. This method works well to find the email’s position and extract it from the text string.
➤ Go to the Developer tab and click on Visual Basic.
➤ In the VBA editor, go to Insert > Module.
➤ Copy and paste the following code into the new module window.
➤ Hit the Run button or press F5 .
Sub ExtractEmails()
Dim cell As Range
Dim matches As Object
Dim regex As Object
Dim match As Object
Dim outputRow As Long
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b"
regex.IgnoreCase = True
regex.Global = True
outputRow = 2
For Each cell In Range("A2:A11")
Set matches = regex.Execute(cell.Value)
If matches.Count > 0 Then
For Each match In matches
Worksheets("VBA").Cells(outputRow, 2).Value = match.Value
outputRow = outputRow + 1
Next match
End If
Next cell
End Sub
This macro uses a regular expression (RegEx) to find all valid email patterns in your data and then lists them in a separate column. After running the code, all emails will be extracted and displayed in column B.
Frequently Asked Questions
Can I use these methods to extract different types of data, like phone numbers?
Yes, you can adapt these methods to extract other types of data by adjusting the delimiter, search criteria, or the regular expression pattern. For example, for phone numbers, you might search for a pattern of digits and hyphens.
What happens if my email addresses have extra spaces?
The TRIM function in the formula will automatically handle leading or trailing spaces. The VBA macro’s regular expression is designed to find valid email patterns without being affected by surrounding spaces.
Is it possible to extract emails from multiple sheets at once?
Yes, you can use Power Query to combine data from multiple sheets and then apply the extraction steps. The VBA macro can also be modified to loop through all worksheets in a workbook.
Concluding Words
Above, we have explored all types of solutions to extract email from text in Excel. By using Excel’s built-in tools, advanced functions, or a powerful VBA macro, you can easily extract email from text. The right method depends on your data structure and how you want to get the result. If you have any queries, feel free to let us know in the comments section below.
