If you need to work through a list of email addresses in order to find email addresses with the same name but different domains, you can do it quickly in Excel.
Table of contents
YouTube Find matching names different domains in email addresses
Sort the list of email addresses
The first step would be to get all the email addresses into a single list and sort them in order. If your list is very small then you can just manually look and see where the first part of the email address is the same (as shown below). However, if it is very large then if would be better that Excel finds where the first part of the email address is the same.
Extract the name of the email address (excluding domain)
We cannot easily ask Excel to compare cells when there is a clear difference (domain names in this case). But we can easily extract the first part of an email address into a separate column and compare that.
As shown below we can use Flash Fill. In the column adjacent to the email addresses (very important), type what you want to see next to one of the email addresses (1), then click on DATA (2) and Flash Fill (3)
The result as shown above is that it quickly separates the name and domain of the email address.
Highlight the email addresses that start with the same name
To make your life easier you can then use Conditional Formatting to highlight any names that are the same (are duplicates). As shown below you will
- Highlight the cells
- Go to the HOME ribbon
- Click on Conditional Formatting
- Choose Highlight Cells Rules
- Choose the Duplicate Values option.
Now all the matching names but different domains will be highlighted and you can work through them to decide which are valid or not!