Email addresses are an important way of communicating in today’s digital world. Excel, a popular spreadsheet program, can be an effective tool for managing and analyzing email addresses. Filtering email addresses in Excel can aid in the sorting and extraction of essential information, such as contacts for a marketing campaign, leads for a sales effort, or newsletter subscribers. This post will walk you through how to filter email addresses in Excel.
How to Filter Email Addresses in Excel: 5 Easy Ways
Email address filtering in Excel can be a useful tool for managing and analyzing vast amounts of data. You may simply filter email addresses based on specified criteria and extract pertinent information by following the four useful methods provided in this article.
Method 1: Nesting the IF Function with Multiple Functions to Filter Email Addresses in Excel
The IF function in Excel is a simple function that lets you conduct conditional computations. Here, we will demonstrate how to filter email addresses based on a set of criteria by using the IF function with the ISNUMBER and the SEARCH function in Excel. So, you can follow the below steps accordingly for a better understanding.
Steps:
- Now, we will apply the following formula in the D5 cell.
=IF(ISNUMBER(SEARCH("@example.com",C5)),B5,"")
Formula Breakdown:
- The SEARCH function searches the text string “@example.com” in cell C5. If the text is found, SEARCH returns the position of the first character in the text where “@example.com” appears. SEARCH returns the #VALUE! error if the text is not found.
- The ISNUMBER function determines whether the SEARCH function returns a number. ISNUMBER returns TRUE if SEARCH returns a number (indicating “@example.com” was located in C5). ISNUMBER returns FALSE if SEARCH returns the #VALUE! error (indicating “@example.com” was not found in C5).
- The IF function determines if the ISNUMBER function’s condition is TRUE or FALSE. The formula returns the value in cell B5 if the condition is TRUE. The formula presents an empty string if the condition is FALSE.
- Now press Enter, and to get all the results, use the Fill Handle icon and drag it down from the D5 cell to the D10 cell.
- In the end, this technique is used to extract data from an email address that has a specified domain name. If the domain name “@example.com” appears in an email address in cell C5, the formula returns the result in cell B5. The formula returns an empty string if the email address in cell C5 does not contain “@example.com“.
Method 2: Combining the TEXTJOIN Function with Multiple Functions to Filter Email Addresses in Excel
Here we will demonstrate how to use the TEXTJOIN function in conjunction with the ISNUMBER and SEARCH functions to filter email addresses in Excel by concatenating (joining together) the names in cells B5 through B10, using a comma and a space as the delimiter, but only for the rows where the corresponding cell in the range C5 through C10 contains the text string “@example.com“.So, you can follow the below steps accordingly for a better understanding.
Steps:
- Now, we will apply the following formula in the D5 cell.
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("@example.com",C5:C10)),B5:B10,""))
Formula Breakdown:
- The SEARCH function searches for the text string “@example.com” in cells C5:C10. If the text is found, the SEARCH function returns the position of the first character in the text where “@example.com” appears. The SEARCH function returns the #VALUE! error if the text is not found.
- The ISNUMBER function determines if the SEARCH function returns a number. If SEARCH returns a number (indicating that “@example.com” was discovered in cell C5:C10), ISNUMBER returns TRUE. ISNUMBER returns FALSE if SEARCH returns the #VALUE! error (indicating “@example.com” was not found in the corresponding cell in the range C5:C10).
- The IF function examines each value in the range B5:B10 and returns the value if the corresponding cell in C5:C10 includes “@example.com“. IF returns a blank value (“”) if the relevant cell in C5:C10 does not contain “@example.com“.
- The IF function returns values that are sent to TEXTJOIN, which concatenates them using a comma and a space as the delimiter. The TRUE argument instructs TEXTJOIN to disregard blank values.
- Now press Enter to get all the results in a row.
- In short, this formula obtains email addresses with the domain “@example.com” from the range C5:C10 and concatenates the matching values in the range B5:B10 using a comma and a space as delimiters. The formula returns the generated string as its output.
Method 3: Nesting the FILTER Function with Multiple Functions to Filter Email Addresses in Excel
Here we will apply the FILTER function with the multiple functions to filter email addresses in Excel for a range of cells (C5:C10) with the domain “@example.com” and return only those. The filtered email addresses are then returned as a vertical array. So, you can follow the below steps accordingly for a better understanding.
Steps:
- Now, we will apply the following formula to the D5 cell.
Formula Breakdown:
- The condition IF(ISNUMBER(SEARCH(“@example.com”, C5:C10)), C5:C10, “”) determines whether the text string “@example.com” appears in each cell in the range C5:C10. If the text is found, SEARCH returns the position of the first character in the text where “@example.com” appears. SEARCH returns the #VALUE! error if the text is not found. ISNUMBER then determines whether the SEARCH result is a number and returns TRUE or FALSE. If ISNUMBER returns TRUE, the value from C5 to C10 is returned. If ISNUMBER returns FALSE, the result is an empty string (“”). The values obtained are sent to the outer IF function.
- IF(C5:C10>””,…, “”) determines whether or not each cell in the range C5:C10 is blank. If a cell is not blank, the associated value is passed to the IF function within it. If a cell is empty, the function returns an empty string (“”). The values obtained are sent to the outer IF function.
- FILTER(…, LEN(C5:C10)>0) filters the IF function’s output values. It only returns values of a length greater than 0 characters (i.e., non-blank cells). The values obtained are returned in a vertical array.
- Now press Enter, and to get all the results, use the Fill Handle icon and drag it down from the D5 cell to the D10 cell.
- In summary, this formula filters a range of cells to return only the cells containing an email address with the domain “@example.com,” and then returns the filtered email addresses as a vertical array.
Method 4: Applying the Filter Command to Filter Email Addresses with Specific Domain in Excel
Now, we will show how to filter email addresses in Excel by using the Filter command from the Data tab. Here, we will show all the domains that contain the specific “@example.com” domain. So, you can follow the below steps accordingly for a better understanding.
Steps:
- To begin with, select the “Email Address” column.
- Then, click on the Filter command from the Data tab. This will apply a filter to the specified column, allowing you to sort and filter email addresses according to certain criteria.
- Now that the filter has been applied, you can filter the email addresses according to your preferences. Click the drop-down arrow next to the email address column heading. This will provide a list of filtering choices, including text, numeric, and date filters. Select the filter that best meets your requirements.
- For example, if you want to filter email addresses that contain a specific domain, such as “@example.com,” or “@gmail.com” you can select the “Text Filters” option and then “Contains.”
- Enter the domain “@example.com” in the text box, and Excel will filter the email addresses to show only those with the chosen domain.
Finally, you will see the output in the below image, where all the specific email addresses are filtered.
Method 5: Using the Data Validation Command to Filter Invalid Email Addresses in Excel
Here, we will apply the Data Validation command to find the invalid email addresses so that we can easily filter the exact email addresses in Excel. So, follow the below steps accordingly.
Steps:
- Firstly, select column A.
- Then, click on the Data Validation command from the Data tab.
- Now, choose the Custom option, apply the following formula, and click OK.
=AND(ISERROR(FIND(" ",A1)),LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))=1,IFERROR(SEARCH("@",A1)<SEARCH(".",A1,SEARCH("@",A1)),0),NOT(IFERROR(SEARCH("@",A1),0)=1),NOT(IFERROR(SEARCH(".",A1,SEARCH("@",A1))-SEARCH("@",A1),0)=1),LEFT(A1,1)<>".",RIGHT(A1,1)<>".")
- Once more, click the tiny triangle next to “Data Validation.” Select the Circle Invalid Data from the menu.
- Finally, you will see that the invalid email addresses have been selected with a red circle. As the operation runs from the A1 cell by default, it also encircles the A1 cell as an invalid email address.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it yourself.
Conclusion
In this article, we’ve covered four ways of how to filter email addresses in Excel. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below. Stay with us and keep Excelling!
<< Go Back to Data | Filter in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
