In this article, we learned in detail about extracting email addresses from Excel including Flash fill, Text to Column feature, Excel formulas and VBA code.
Extracting email addresses is beneficial when working with a large dataset and you need to separate the email addresses for further work. With extracting email addresses, you can streamline communication efforts and send bulk emails for various purposes.
In the following, you will find an overview of extracting email addresses from Excel.
How to Extract Email Addresses from Excel: 4 Ways
In the following, I have shared 4 simple ways to extract email addresses from Excel. Suppose we have a dataset containing Employee Name, Email Address, and Contact Number. Now we will extract email addresses using Excel tools, built-in functions, and formulas.
1. Extracting Email Addresses Using Excel Tools
We can do it following two ways-
1.1 Using Flash Fill
Flash fill is a wonderful feature that can be incredibly helpful when it comes to extracting email addresses from a column of data. It uses pattern recognition and intelligent data analysis algorithms to automatically fill data based on patterns it identifies.
- First, type your desired content to the next side-by-side column. Here, I wrote the email address in cell (C5).
- Second, selecting cells (C5:C12)) click Flash Fill from the Home ribbon.
- Within a glimpse of an eye, you will get your desired result by extracting email addresses from the table. Simple isn’t it?
1.2 Utilizing Text to Columns Feature
When working with Excel spreadsheets, extracting specific information, such as email addresses, from a column of data can be a common requirement. Luckily, Excel provides a versatile feature called Text to Columns that allows you to split text into separate columns based on specified criteria.
- To start with, choose the table from which you want to extract email addresses.
- Next, click Text to Columns from the Data tab.
- From the Convert Text to Columns Wizard window press Next.
- In the next step, put a comma (,) into the Others section and click Next.
- Hence, choose your destination cell ($C$5), checkmark the Do not import column option to skip multiple columns and press Finish.
- Finally, we have successfully extracted email addresses from Excel. It’s that simple.
Read More: How to Make an Address Book in Excel
2. Extracting Email Addresses from a Single Column
We can do it following three ways-
2.1 Utilizing TRIM, MID, SUBSTITUTE, REPT, LEN, LEFT, SEARCH Functions
Functions such as TRIM, MID, SUBSTITUTE, REPT, LEN, LEFT, and SEARCH can be leveraged to extract email addresses from unstructured text and transform them into a usable format.
- Simply, choose a cell (C5) and apply the below formula down.
=SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(B5)," ",REPT(" ",200)), (LEN(LEFT(B5,SEARCH("@",B5)))-LEN(SUBSTITUTE(LEFT(B5,SEARCH("@",B5))," ", "")))*200+1, 200)), ",", "")
Formula Breakdown
- LEFT(B5,SEARCH(“@”,B5)
Here, the formula finds the text from the beginning of cell (B5) up to the “@” symbol.
- SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”)
This section removes any spaces from the text obtained in step 1. It replaces each space (” “) with an empty string (“”).
- LEN(SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”))
This portion calculates the length of the text obtained in step 2 and counts the number of characters in the text.
- (LEN(LEFT(B5,SEARCH(“@”,B5)))-LEN(SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”)))
This step subtracts the length of the text without spaces from the length of the original text.
- REPT(” “,200)
It generates a string consisting of 200 spaces repeating the space character (” “) 200 times.
- SUBSTITUTE(TRIM(B5),” “,REPT(” “,200))
This replaces each occurrence of a single space in cell (B5) with 200 spaces. Then the TRIM function removes any leading or trailing spaces.
- MID(SUBSTITUTE(TRIM(B5),” “,REPT(” “,200)), (LEN(LEFT(B5,SEARCH(“@”,B5)))-LEN(SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”)))*200+1, 200)
This extracts a portion of the modified text obtained in step 6. Hence, it starts from a specific position and retrieves a substring of length 200.
- TRIM(MID(SUBSTITUTE(TRIM(B5),” “,REPT(” “,200)), (LEN(LEFT(B5,SEARCH(“@”,B5)))-LEN(SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”)))*200+1, 200)), “,”, “”)
This trims any leading or trailing spaces from the substring obtained in step 7.
- SUBSTITUTE(TRIM(MID(SUBSTITUTE(TRIM(B5),” “,REPT(” “,200)), (LEN(LEFT(B5,SEARCH(“@”,B5)))-LEN(SUBSTITUTE(LEFT(B5,SEARCH(“@”,B5)),” “, “”)))*200+1, 200)), “,”, “”)
This replaces any commas (“,”) in the trimmed substring with empty strings (“”).
- Then, hit the ENTER key and drag the fill handle down to get the final output.
2.2 Applying TEXTJOIN, CHAR, IF, ISERROR, SEARCH, TRIM, MID, SUBSTITUTE, REPT, LEN, LEFT, SUBSTITUTE Functions
If you are looking for a simple solution to extract email addresses from multiple cells then you can combine the TEXTJOIN, CHAR, IF, ISERROR, SEARCH, TRIM, MID, SUBSTITUTE, REPT, LEN, LEFT functions too.
- Similarly, select a cell (C5) and write the following formula inside the cell.
=TEXTJOIN(CHAR(10),TRUE,IF(ISERROR(SEARCH("@",B5:B12)),"",TRIM(MID(SUBSTITUTE(TRIM(B5:B12)," ",REPT(" ",200)),(LEN(LEFT(B5:B12,SEARCH("@",B5:B12)))-LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH("@",B5:B12))," ","")))*200+1,200))))
Formula Breakdown
- SEARCH(“@”,B5:B12)
Here the SEARCH function searches for the “@” symbol in cells (B5:B12) and returns an array of values, indicating the position of “@” in each cell.
- ISERROR(SEARCH(“@”,B5:B12))
This function checks if an error occurs in the search operation. It returns an array of TRUE or FALSE values, where TRUE represents an error and FALSE represents a successful search.
- IF(ISERROR(SEARCH(“@”,B5:B12)),””,TRIM(MID(SUBSTITUTE(TRIM(B5:B12),” “,REPT(” “,200)),(LEN(LEFT(B5:B12,SEARCH(“@”,B5:B12)))-LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””)))*200+1,200))))
Here an IF statement checks the array from step 2. If an error is found, it returns an empty string (“”). Otherwise, it proceeds with the next part of the formula.
- LEFT(B5:B12,SEARCH(“@”,B5:B12))
This extracts the text from the beginning of each cell in the range B5 to B12 up to the “@” symbol.
- SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””)
This removes any spaces from the text obtained in step 4. It replaces each space (” “) with an empty string (“”).
- LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””))
This calculates the length of the text obtained in step 5 for each cell and returns an array of values representing the length.
- (LEN(LEFT(B5:B12,SEARCH(“@”,B5:B12)))-LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””)))*200+1
This part calculates the starting position for the MID function. Then, it subtracts the length of the text without spaces from the length of the original text and multiplies the result by 200.
- REPT(” “,200)
This generates a string consisting of 200 spaces. After that, it repeats the space character (” “) 200 times.
- SUBSTITUTE(TRIM(B5:B12),” “,REPT(” “,200))
This replaces each occurrence of a single space in the original text (B5:B12) with 200 spaces. The TRIM function removes any leading or trailing spaces.
- MID(SUBSTITUTE(TRIM(B5:B12),” “,REPT(” “,200)),(LEN(LEFT(B5:B12,SEARCH(“@”,B5:B12)))-LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””)))*200+1,200)
This extracts a portion of the modified text obtained in step 9. It starts from the position calculated in step 7 and retrieves a substring of length 200 for each cell.
- TEXTJOIN(CHAR(10),TRUE,IF(ISERROR(SEARCH(“@”,B5:B12)),””,TRIM(MID(SUBSTITUTE(TRIM(B5:B12),” “,REPT(” “,200)),(LEN(LEFT(B5:B12,SEARCH(“@”,B5:B12)))-LEN(SUBSTITUTE(LEFT(B5:B12,SEARCH(“@”,B5:B12)),” “,””)))*200+1,200))))
This function concatenates the array of trimmed substrings obtained in step 11 into a single text string, separated by a line break (CHAR(10)). The TRUE argument indicates that empty values should be ignored.
- Gently, hit ENTER and pull the Fill Handle down to fill the column.
- As a result, we have successfully extracted email addresses from multiple cells.
2.3 Combining SEARCH, ISERROR, IF and TEXTJOIN Functions
With the proper combination of the SEARCH, ISERROR, IF and TEXTJOIN functions you can extract email addresses.
- In the same fashion, choose a cell (C5), write the below formula and drag the FIll handle down.
=IF(ISERROR(SEARCH("@", B5)), "", MID(B5, SEARCH(",", B5) + 2, LEN(B5) - SEARCH(",", B5) - 1))
Formula Breakdown
- ISERROR(SEARCH(“@”, B5))
The SEARCH function searches for the “@” symbol in cell (B5) and returns the position of “@” in the text. The ISERROR function checks if an error occurs in the search operation.
- IF(ISERROR(SEARCH(“@”, B5)), “”, …)
This is an IF statement that checks if an error occurs. If an error is found it returns an empty string (“”).
- MID(B5, SEARCH(“,”, B5) + 2, LEN(B5) – SEARCH(“,”, B5) – 1)
Finally, the extraction of a portion of the text in cell (B5) happens. Then, it starts from the position calculated in step 2 and retrieves a substring of length obtained.
- Finally, we have the extracted email addresses in our hands.
Read More: How to Format a Column for Email Addresses in Excel
3. Extracting Email Addresses from Multiple Columns
We can do it following three ways-
3.1 Using TEXTJOIN, IF, ISERROR, SEARCH Functions Together
In order to extract email addresses from multiple cells with one click you can use the combination of TEXTJOIN, IF, ISERROR, and SEARCH functions.
- Choose a cell (F5), write the below formula, and hit ENTER to get the output.
=TEXTJOIN(", ", TRUE, IF(ISERROR(SEARCH("@", B5:D12)), "", B5:D12))
Formula Breakdown
- ISERROR(SEARCH(“@”, B5:D12))
The SEARCH function searches for the “@” symbol in cells (B5:D12) and returns the position of “@” in the text. The ISERROR function checks if an error occurs in the search operation.
- IF(ISERROR(SEARCH(“@”, B5:D12)), “”, B5:D12)
Here the IF statement checks if an error is found and returns an empty string (“”). Otherwise, it returns the corresponding cell value from the cells (B5:D12).
- TEXTJOIN(“, “, TRUE, IF(ISERROR(SEARCH(“@”, B5:D12)), “”, B5:D12))
This function concatenates the array of values into a single text string, separated by a comma and a space (“, “).
3.2 Merging FILTER, TOCOL, ISNUMBER, SEARCH Functions
You can also combine FILTER, TOCOL, ISNUMBER, and SEARCH functions to extract email addresses from multiple locations in a spreadsheet.
- Choosing a cell (F5), put the following formula and hit ENTER to get the result.
=FILTER(TOCOL(B5:D12),ISNUMBER(SEARCH("@",TOCOL(B5:D12))))
Formula Breakdown
- SEARCH(“@”,TOCOL(B5:D12)
Here the TOCOL function converts the range B5 to D12 into a columnar array. Then the SEARCH function searches for the “@” in each cell on the columnar array.
- ISNUMBER(SEARCH(“@”, TOCOL(B5:D12)))
Here the ISNUMBER function checks if an error occurs in the search operation and returns an array of TRUE or FALSE values.
- FILTER(TOCOL(B5:D12), ISNUMBER(SEARCH(“@”, TOCOL(B5:D12))))
This part applies a filter to the columnar array using the TRUE and FALSE values and returns a new array that includes only the cells where “@” is found.
3.3 Combining INDEX, MATCH, COLUMN, SEARCH, ISERROR, IF and MIN Functions
In some situations, you can also combine the INDEX, MATCH, COLUMN, SEARCH, ISERROR, IF, and MIN functions to separate email addresses.
- Simply, select a cell (F5), place the following formula and press ENTER.
=INDEX(B5:D5,1,MIN(IF(ISERROR(SEARCH("@",B5:D5)),"",MATCH(COLUMN(B5:D5),COLUMN(B5:D5)))))
Formula Breakdown
- ISERROR(SEARCH(“@”, B5:D5))
The SEARCH function returns the position of the “@” symbol within each cell, and the ISERROR function checks if an error occurs. If there is an error it returns TRUE, otherwise, it returns FALSE.
- IF(ISERROR(SEARCH(“@”, B5:D5)), “”, MATCH(COLUMN(B5:D5), COLUMN(B5:D5)))
The IF function performs a conditional check. If the result of the previous step is TRUE, it returns an empty string (“”). Otherwise, it proceeds with the MATCH function.
- MATCH(COLUMN(B5:D5), COLUMN(B5:D5))
The MATCH function is used to find the relative position of the current column within the range B5:D5. The COLUMN(B5:D5) part returns an array of column numbers {2, 3, 4}, and MATCH compares each column number with the array itself.
- MIN(IF(ISERROR(SEARCH(“@”, B5:D5)), “”, MATCH(COLUMN(B5:D5), COLUMN(B5:D5))))
Finally, the MIN function is used to find the smallest value from the array returned by the previous step. If there are no errors, it returns the position of the leftmost column with the “@” symbol.
- INDEX(B5:D5,1,MIN(IF(ISERROR(SEARCH(“@”,B5:D5)),””,MATCH(COLUMN(B5:D5),COLUMN(B5:D5)))))
The INDEX function is used to retrieve the value from the range B5:D5. It takes the row number as 1 and the column number as the result of the previous steps.
- Finally, we have successfully extracted email addresses from multiple cells. It’s that simple.
Read More: Formula to Create Email Address in Excel
4. Applying VBA Code with User-Defined Function
When it comes to extracting email addresses from a column of data in Excel, sometimes the built-in functions may not provide the level of flexibility or customization required. In such cases, Visual Basic for Applications (VBA) can be employed to create a user-defined function (UDF) that automates the extraction process.
- First, open the VBA window and insert a module to write a VBA code.
- Then, inside the module put the below code down and save it.
Function Extract_Email_Address(emailString As String) As String
emailPattern = "\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}\b"
Set regex = CreateObject("VBScript.RegExp")
With regex
.Pattern = emailPattern
.Global = True
End With
Set matches = regex.Execute(emailString)
If matches.Count > 0 Then
Extract_Email_Address = matches(0)
Else
Extract_Email_Address = ""
End If
End Function
- Now, coming back to the workbook, choose a cell (D5) and write the newly created user-defined function.
=Extract_Email_Address(B5)
- Simply, hit ENTER and drag down the fill handle to extract email addresses.
How to Extract Username from Email Addresses in Excel
Suppose we have a dataset of Email Addresss and some Contact Numbers with a separator in the same cell. Now, we will extract only the username from the email addresses in Excel.
- First, select the whole table and visit the Text to Columns feature from the Data tab.
- From the new window, choose Delimited and hit Next.
- Hence, checkmark the Comma option and put @ in the Other section.
- Thereafter, choose your destination cell, skip some columns, and hit Finish.
- Finally, we have the user name extracted from the email addresses in Excel.
Things to Remember
- If you are using Windows then you can apply the Flash fill feature for extracting email addresses. In Mac devices, the flash fill feature won’t work.
Frequently Asked Questions
1. Can I extract email addresses with specific criteria, such as filtering by domain or filtering out duplicates?
You can filter by domain using text filters or utilize built-in functions to remove duplicates from the extracted email addresses.
2. Can I extract email addresses from multiple Excel files at once?
you can consolidate the data from multiple files into a single Excel file and then extract the email addresses.
3. Can I automate the process of extracting email addresses from Excel?
With automation, you can create scripts or programs to extract email addresses from Excel files in a batch process, saving time and effort.
Download Practice Workbook
You can download our practice workbook from here for free!
Conclusion
In conclusion, extracting email addresses from Excel provides a streamlined approach for managing, analyzing, and utilizing email data. You can integrate email addresses into various applications for effective communication. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience.