Create email address from name

One of the key skills you need to be good with Excel formulas is concatenation. Put simply, concatenation is just a fancy name for joining text together. In Excel formulas, the primary operator for concatenation is the ampersand (&). A good example of a simple concatenation task is the creation of an email address using a first and last name. There are many ways to create an email address, but the core problem is to join together a name and a domain, as seen in the worksheet shown. The formula in E5, copied down, is:

=LOWER(LEFT(C5)&B5)&"@"&$E$2

Background study

• How to concatenate in Excel - article
• LEFT function - overview
• LOWER function - overview
• How to change case with UPPER, LOWER, and PROPER - video

How the formula works

In the example shown, the formula in E5 is:

=LOWER(LEFT(C5)&B5)&"@"&$E$2

Working from the inside out, the LEFT function is used to get the first character of the first name like this:

LEFT(C5) // returns "T"

LEFT extracts text from the left side of a text string. Normally, we would also give LEFT the number of characters to extract as num_chars argument. However, in this case, we only want the first character and it turns out that num_chars defaults to 1, so there is no need to provide a number. With "Tom" in cell C5, the LEFT function returns "T". We then use the concatenation operator (&) to combine the result from LEFT with cell B5:

=LEFT(C5)&B5
="T"&"BROWN"
="TBROWN"

The result is the text string "TBROWN", which is returned directly to the LOWER function. Simplifying, at this point we have the following:

=LOWER("TBROWN")&"@"&$E$2

The LOWER function converts any uppercase characters in a text string to lowercase characters. In this case, LEFT converts "TBROWN" to "tbrown":

=LOWER("TBROWN") // returns "tbrown"

Simplifying again, we now have:

="tbrown"&"@"&$E$2

The remaining formula concatenates the text "tbrown" to the "@" character, and the result is then concatenated to cell E2, as shown below:

="tbrown@"&$E$2
="tbrown@"&"abc.com"
="tbrown@abc.com"

With "abc.com" in cell E2, the final result is "tbrown@abc.com". Notice that the reference to cell $E$2 is an absolute reference to prevent this cell from changing as the formula is copied down the column.

Alternate email address schemes

In a work environment, there are many different schemes for creating an email address. For example, a name like "Tom Brown" may appear as tbrown@abc.com, tom.brown@abc.com, brown_tom@abc.com, tombrown@abc.com, etc. The attached worksheet contains formulas for these alternatives:

=LOWER(LEFT(C5)&B5)&"@"&$E$2 // tbrown@abc.com
=LOWER(C5&"."&B5)&"@"&$E$2 // tom.brown@abc.com
=LOWER(B5&"_"&C5)&"@"&$E$2 // brown_tom@abc.com
=LOWER(C5&B5)&"@"&$E$2 // tombrown@abc.com

Notice all variations use concatenation in different ways to create a different email address. They also use the LOWER function to force all parts of the name to lowercase characters only.

CONCAT function

If you prefer, you can also use the CONCAT function to solve this problem like this:

=LOWER(CONCAT(LEFT(C5),B5,"@",$E$2))

CONCAT joins all four values with concatenation without the need for the & operator. The result from CONCAT, "TBROWN@abc.com", is returned directly to the LOWER function:

=LOWER("TBROWN@abc.com") // returns "tbrown@abc.com"

The final result is "tbrown@abc.com" as in the original formula above. Note that in this formula, we run the entire text string through the LOWER function to keep things simple.

Note: In an old version of Excel without CONCAT, you can use the CONCATENATE function with the same result.