Managing email lists can be made more efficient by identifying unique company domains. This process can help you quickly understand the sources of your emails, and Excel’s features like Flash Fill and Remove Duplicates make this task simple and fast.
Table of contents
YouTube: Extract unique domain names from list of email addresses.
Extracting Company Domains Using Flash Fill.
Start with your list of email addresses in Excel. The goal is to extract the company or domain name from each email address.
As per the above image, in the adjacent column (next to your email list), type the domain name of the first email address manually. For example, if your email address is ” a.gaither@tresstrong.com”, type “tresstrong.com “. Ideally ensure that the format matches the email address.
Go to the Data tab on the Excel ribbon and click on Flash Fill. You can also use the shortcut Ctrl + E. Excel will automatically extract the domain names for all email addresses in the list.
Removing Duplicate Domain Names.
Now that you have a column with the extracted domain names, you’ll likely notice duplicates. Highlight the entire column of domain names and copy it.
Paste the copied domain names into a new column to keep the original data intact.
As per the above image, highlight the pasted column, go to the Data tab, and click on Remove Duplicates. In the dialog box that appears, confirm that you want to remove duplicates from the selected column and click OK. Excel will remove all duplicate entries, leaving you with a list of unique domain names.
Counting Emails by Domain Using COUNTIFS.
With your unique domain names in place, you might want to know how many emails came from each domain. Use the COUNTIFS function to count the occurrences of each domain in your original email list.
As per above, in a new column, start by typing =COUNTIFS(. Select the range of your original domain names (the column where you used Flash Fill). Add dollar signs ($) to lock the range, ensuring it doesn’t change as you copy the formula down. For the criteria, select the cell containing the unique domain name you want to count. Complete the formula and press Enter. The result will show how many times that domain appears in your email list. Copy the formula down to count all unique domains.
Tips and Troubleshooting.
- Consistency in Domain Names: Try ensure the domain names are consistently formatted. For example, “gmail.com” should not appear as “Gmail.com” in some entries. Consistency will help Flash Fill work more effectively. You could use UPPER, PROPER or LOWER functions to make all text consistent.
- Flash Fill Issues: Flash Fill works best when the example provided is clear and directly adjacent to the column of data you want to split.