Excel Formula to Create an Email Address

Formula to Create an Email Address

I performed a good amount of link outreach when I was working in SEO. In Microsoft Excel, I would put down every first and last name of every blogger that I wanted to reach out to. Most of the time, I would find a very generic email address for the blog site that would be something like support@domainname.com or something like that. For me, I did not really like that approach. It sort of felt generic. So in excel, I used the concatenate formula to see if I could figure out their actual email address.


How to Simply Create an Email Address Formula in Excel


In this example below, I have the first name, the last name, and I also have the domain that the blogger wrote for. Here is the formula that I am gong to use.


Formula to Create an Email Address


How Does this Formula Work?


The first part of the formula takes the cell values from column A2 and column B2 and I am telling excel to put a period in the middle of those two columns. The reason why I want to do this is that at Grasshopper, my email address looked like this greg.kristan@grasshopper.com. If I did not put the period in the middle, my formula would just put my first and last name together without that space. Again, I know that every website is different, but I am looking to see how many email addresses I can find that follows this rule. If they do not follow this rule, we can get creative with our formulas, but I am going to keep it very basic for the blog post.


The second part of the formula is making sure the @ symbol is part of the email address. Since this is an email address that we are trying to create, we need to put that symbol in this formula.


The last part of the formula is adding the domain.com part to the concatenate formula. Since we are trying to get the email address, we are going to finish up the formula with that part.


How to Make the Email Address Clickable in Excel?


Ok, so we have the email address in excel. Let’s get ready to send off some emails. We could manually click on every cell and press enter to make that particular cell clickable, but that could take forever. Let’s let excel do it’s magic.


Step 1: Highlight the Entire Column

Step 2: Head to the Cell Styles section

Step 3: Select the Hyperlink option to make everything selected into a hyperlink action.


This picture below shows where I went to make this.


How to Make a Clickable Email Address in Excel




Not every email address will look like first name.last name @domain.com, but it is a good start non the less. Also, I want to stress that this approach is incredibly efficient. If you are looking for hundreds of emails, you can honestly be wasting your time. I would suggest starting with this formula and document what happens when you send the email. You can set up a hidden column that simply states if the email bounced or not. If it did, you can get creative with trying a new email set up.


I want to also give a shout out to a free plugin that tracks email opens. This plugin is free and it is called Sidekick from Hubspot. I used this all the time at Grasshopper to see if my emails were being opened or not. I used this a lot to perform A/B tests with different email subject headlines to see what would get opened.