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.
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