Updated on November 4, 2019
If you're trying to find someone's email address, you may get a list back with email addresses of all of your prospects. However, for your email templates, might want to reference the domain of the email address. That is way too tedious to do by hand. Here's a simple Excel trick to get the domain from an email address.
If A1 is the cell that has the email address, then the formula to get the domain is simply:
=MID(A1,FIND("@",A1)+1,99)
Let's break this down.
MID is an Excel function that returns a specified numbers characters back from a string, starting at a specified place. In our case, we want to start from immediately after the "@" symbol in the email address, and go to the end of the string. In simple terms, that's all this function does.
The MID function takes three inputs:
- The string to look at
- The starting position
- The number of characters to take from that starting position
Let's dissect the second input. Here we put a function, FIND, that returns the first position of a specified character in string. Here we use FIND to get the location of the "@" symbol. It takes two inputs, the text to analyze (the "haystack") and the character to find ("the needle"). In our case, A1 is the haystack and "@" is the needle.
We add +1 to the FIND result because we don't want to include the "@" symbol in the domain. Without it, the formula on "ryan@toofr.com" would return "@toofr.com" and we don't want that.
The 99 I put at the end is just a hack to go all the way to end of the string. No email domains will actually be 99 characters long, but I put that just to be safe.
And there you have it!