Welcome to Excel Jet Consult blog post. In this Microsoft Excel blog post, I will demo how to generate Email Addresses using different examples including Power Query.

Video

Sample Data

In the caption below, we have sample HR dataset. We want to generate email addresses for each of the Employees using Six different methods.

Ampersand

First, we are going to look at performing the email addresses generation using the ampersand sign

In cell E2, execute the formula: =LOWER(B2&”.”&C2&”@yahoo.com”)
Click Enter and copy down the formula

Note, we used the LOWER Function to convert the result to all lowercase

Flash Fill

We are going to use the Flash Fill to generate the email addresses

In cell F2, type in the following: ferris.mary@yah00.com
Click Enter to move to the cell below (cell F3)
Deploy CTRL + E which represent the Flash Fill shortcut

CONCAT FUNCTION

Next, we are going to use the CONCAT Function to generate the email addresses

The CONCAT function concatenate a list or range of text strings

In cell G2, execute the formula: =LOWER(CONCAT(B2,”.”,C2,”@yahoo.com”))
Click Enter

TEXTJOIN FUNCTION

Next, we are going to consider doing the same operation using TEXTJOIN Function

In cell H1, execute the formula: =LOWER(TEXTJOIN(,,B2,”.”,C2,”@yahoo.com”))
Click Enter

CONCATENATE FUNCTION

Finally for worksheet function, we are going to use CONCATENATE Function

In cell I2, execute the formula: =LOWER(CONCATENATE(B2,”.”,C2,”@yahoo.com”))
Click Enter

Power Query

Finally, we are going to use Power Query. The first thing we’d want to do is format the data as an Excel Tables and load to the Power Query

Click inside the Data and deploy CTRL + T
In the Tables dialogue box, click on OK
Right-click in the formatted data and choose Get Data From Sheet

The data is loaded into the Power Query as seen below

Click on Add Column tab
Click on Custom Custom
In the formula bar, type in Email PQ as column name
In the formula. execute the following: =Text.Lower(Text.Combine({[Last],”.”,[First],”@yahoo.com”}))

Next, we want to get rid of the other columns

Select all the columns except the Email PQ holding SHIFT key
Right-click and choose Remove Columns

We only have one column as seen below

Finally, we going to load the result into Excel.

Therefore, in the Home tab, Click on Close & Load and Close and Load To…

Select Existing Worksheet and cell J1

Click OK

In the caption below, we can see the PQ result

See you in the next tutorial

Generate Email Addresses in Microsoft Excel [Six Examples + Power Query]