Extract top domain name from email addresses using Excel

Question

I want to extract certain types of domain names from email address like this:

[e-mail]                  cox.net
[e-mail]               cox.edu
[e-mail]                      cox.com
[e-mail]           csun.edu
[e-mail]             csun.edu

How do I do this using Excel? All of the answers I've seen around the 'Net fail to consider the fact that some domains have more than one level. I do not want the name that appears before the second period from the right. Some email addresses only have one period between the @ sign and the TLD while others may have more than one period in between.

~Doug

Answer 1

This can easily be done with formulas.

To avoid duplicating calculations I suggest setting up a helper column that extracts the text after the @ sign, which will be referred to several times in the formula.

If the email address is in A1, put this helper formula into cell B1:

=MID(A1,FIND("@",A1)+1,99)

The next step is to count the number of . characters in B1. A domain with a sub-domain will have 2, so only if the count is greater than 1, you need to cut off everything before and including the first . character.

=IF(LEN(B1)-LEN(SUBSTITUTE(B1,".",""))>1,MID(B1,FIND(".",B1)+1,99),B1)

The screenshot shows this formula combination in action. Of course, you could replace every mention of B1 with the formula in B1, but that would be harder to maintain and take longer to calculate (not that it matters with a few hundred email addresses).