Sourcer Excel Tip #3 – Automatic Email Address Creation

In Part’s 1 and 2 of this Excel series we covered how to manipulate your list of names by either combining or separating the columns. Let’s take that one step further now in the coolest hack of all, automatic email address creation. Through the use of Excel formulas you can take a list of names plus a company email syntax and generate a list of corporate email address. When I finally constructed the correct formulas for each of these I felt that I had hit the jackpot.

Sourcer Tip for Excel #3 – Email Address Creation

Where can this tip come in handy? When you’ve spent time on name generation or org charting of a target company (we’ll call it Company A) and have developed a list of employees whom you’d like to contact. You have also discovered the email syntax that Company A uses. Using the tips taught in Part 2 you can separate out the first and last names in an effort to quickly create a complete list of email address. How? Through the use of one of the following formulas.

Formulas

Email Format: FirstName.LastName@company.com (john.doe@companyA.com)
=A1&”.”&B1&”@companyA.com”

Email Format: FirstInitialLastname@company.com (jdoe@companyA.com)
=LEFT(A1, 1)&B1&”@companyA.com”

Email Format: FirstNameLastInitial@company.com (johnd@companyA.com)
=A1&(LEFT(B1,1))&”@companyA.com”

Email Format: FirstName@company.com (john@companyA.com)
=A1&”@companyA.com”

Email Format: FirstNameLastName@company.com (johndoe@companyA.com)
=LEFT(A1, 1)&B1&”@companyA.com”

Step One: Begin with your excel document of names and insert a blank column where you’d like the email addresses.

Step Two: Choose the formula that matches your target company syntax and enter the formula in the first row destination cell. Making sure that A1= The column of First Names and B1= The column of last names. If your data begins in different columns or rows simply adjust the cell reference in the formula to match your data.

Step Three: Replace the @companyA.com with your target company domain and copy the formula down the rest of the column. You now have a complete list of Names and Email address to run a mail merge with or import into your database.

Doesn’t get much easier.

Article Continues Below

Tip: Don’t ever type your formulas for this in Word and expect to copy them into Excel. Word does quotations marks different than is required for Excel or Boolean. So make sure to type your formula directly into the excel cell.

What Do These Formulas Mean?

These formulas use two key Excel functions & and LEFT.

The & Function is used to string multiple pieces of data together into one cell. =A&B&C

The LEFT function is used to tell Excel you want the Data from cell X but you only want Y number of characters from that data starting from the left, written LEFT(X,Y). So LEFT(A2,1) will give you only the first character of cell A2. LEFT(A2, 4) will give you the first 4 characters. Conversely the RIGHT function will do the same thing just reading the data from the right.

Try these out and if you think of other uses for these functions and share them with us in the comments.

If you found this article helpful, please share with others. Thanks!

email image from freedigitalphotos.net

Jennifer is an accomplished talent acquisition professional with a strong background in strategic sourcing and recruiting. She has worked as a sourcer, researcher, and recruiter for boutique executive search firms and Fortune organizations. She is currently a Senior Recruiter at Amazon. Connect with Jennifer on LinkedIn and Twitter @JennBowen.

Topics

7 Comments on “Sourcer Excel Tip #3 – Automatic Email Address Creation

  1. This formula is OK, I do the same with a Concatenate formula imbedded in a counta so that the lines with no names will not generate an email address. The reason I stumbled onto this was the generated email name will not appear as an email link… I was hoping to change that.

  2. i do have a auto create excel document. where we have to create 20 patterns for one email. we do not know which pattern works. some times we get two email patterns valid. Thats fine when we do manual email validation. But, what my questions is how do you validate list (20 emails for 1 lead or may be less) and copy the valid email in another column. Is there any free software or bot does this work. No need to validate emails. The software or Bot have to create email pattern based on google email pattern searches. I would really appreciate if you could let me know if anyone have.

  3. Can you do the reverse re what if you have the e-mail addresses but need to separate and populate the the first name and last name columns?

Leave a Comment

Your email address will not be published. Required fields are marked *