Excel – Convert Names to Email Addresses

Microsoft Excel, Microsoft Office

Converting Names into Email Addresses

Suppose you have a list of names, perhaps a roster of employee names, and you wish to generate email addresses for these individuals. If you work at a company that has an established standard for email addresses (i.e. first initial of first name with last name) then you have a few options. The preferred strategy depends largely on the version of Excel you are using as well as the naming pattern used in the emails addresses.

Flash Fill (Excel 2013 / Excel 2016)

If you are not familiar with Flash Fill, this tool allows you to type a pattern next to existing data and Flash Fill will repeat the pattern for the remaining data but on a per-record/per-line basis.

Let us take a look at the following example:

You have a list of first and last names and you wish to convert those names to an email format that takes the first letter of the first name, adds a “dot”, then adds the last name with an “@” sign and the company domain name. If we had an employee named “Fred Smith” who worked at “widget.com”, we would need to assign the email address “f.smith@widget.com” to the user.

Imagine a list like the following:

After you have manually typed the email address for the first user, click the Data tab and then in the Data Tools group, click the Flash Fill button (or press CTRL-e on the keyboard.)

You will see all of the remaining email addresses generated based on the pattern of the first email address.

Flash Fill is a fantastic tool for shortcutting data entry based on existing data, but it is not without its flaws. If you wanted to generate email addresses that are the same as above but without the period, the Flash Fill tool gets a little “iffy” and assumes you want the first person’s initial to be used throughout all of the remaining email addresses.

This is obviously a problem. Additionally, because Flash Fill is a one-time creation of data, if you add names to the list, or modify existing names due to spelling errors or name changes, those names will not automatically generate email addresses.

Enter the Formulas (all Excel versions)

If you are running Excel 2010 or earlier, or you would like to have the list monitored for additions or changes, a formula may be the tool you need.

There is no “magic bullet” function that will perform this task, but with a combination of a few functions, a “super-formula” can generate the desired result.

Let’s first identify the components of the email and then determine the functions needed to assemble the pieces.

First initial of the first name

This can be extracted with the LEFT function. The LEFT function extracts letters from text starting from the left side. The only information required is the text to extract from and the number of characters to extract. If we wanted to get the first letter from the name in cell A2, the formula would look like the following:

=LEFT(A2,1)

Full last name

Since we want the last name (located in cell B2) in its unaltered entirety, we will just concatenate the last name to the result of the LEFT function’s result. (NOTE: addition to formula in RED)

=LEFT(A2,1)&B2

Domain name

Next, we will concatenate the “@” sign with the company domain name “widget.com” to the previous step’s result.

=LEFT(A2,1)&B2&”@widget.com”

This is normally where most people would stop, but the result would yield capital letters in our email addresses.

Convert all letters to lower-case

The cherry on top is to place the current formula inside a LOWER function. This function’s job is to convert all text to lower case.

=LOWER(LEFT(A1,1)&B2&”@widget.com”)

After executing a fill-series to repeat the newly created formula down the entirety of the column, we now have a list with all of the names in a format that meets corporate naming conventions.

A word of warning

If you have two or more employees with the same last name and the same first initial of the first name, this will not create unique email addresses for these individuals. It would be possible to create a much more complex formula to detect such occurrences and assign a unique number to each duplicate (i.e. jsmith1, jsmith2, etc…), but for this tutorial a simpler approach would be to apply Conditional Formatting / Duplicate Values… on the column of email addresses to flag any email addresses that occur more than once.

Once the duplicates have been identified, manual changes can be made based on the standardized email rules.