Formula to Create Email Address in Excel (2 Methods)

Suppose you have the following dataset.

Dataset to Create Email Address in Excel


Method 1 – Combine CONCATENATE, LOWER, and LEFT Functions to Create Email Addresses

Steps:

  • Click on the relevant cell (E5 in this example).

Select Cell to Insert Formula to Create Email Address

  • Enter the formula below.
  • Press Enter.
=CONCATENATE(LOWER(LEFT(B5)),LOWER(C5),"@",$D$5)

CONCATENATE Formula to Create Email Address in Excel

Formula Breakdown:

LEFT(B5):
It trims the B5 cell to one character.

(LOWER(LEFT(B5)):
It lowercases the first letter extracted by the LEFT function.

LOWER(C5):
It lowercases the letters of the C5 cell.

=CONCATENATE(LOWER(LEFT(B5)),LOWER(C5),”@”,$D$5)
It combines the lowercase first letter of the B5 cell, the lowercase text of the C5 cell, the ‘@’ symbol, and the D5 cell which is the domain name.

  • Check that the result is a properly formatted email address.
  • Use the Autofill tool to copy the formula to the rest of the column.

Copy Formula to Create All Email Addresses

The result should be a column of correctly formatted email addresses.

Created Email Address Using CONCATENATE Function

Note:

You can also use the CONCAT function in place of the CONCATENATE function.

Read More: How to Make an Address Book in Excel


Method 2 – Combine the Ampersand (&) Symbol with the LOWER and LEFT Functions to Generate Email Addresses

Steps:

  • Choose the relevant cell (E5 in this example).

Select Cell where You want to Insert the Formula

  • Enter the following formula.
=LOWER(LEFT(B5)&C5)&"@"&$D$5
  • Press Enter.

Formula to Create Email Address in Excel Using Ampersand Symbol

Formula Breakdown:

LEFT(B5):
This returns the first letter of the B5 cell.

LOWER(LEFT(B5)&C5):
This combines the first letter of the B5 cell and the value of the C5 cell and lowercases the combination.

LOWER(LEFT(B5)&C5)&”@”&$D$5
This combines the previous result with the ‘@’ symbol and the domain name.

  • Check that the result is a properly formatted email address.
  • Use the Autofill tool to copy the formula to the rest of the column.

Drag Fill Handle to Copy Formula

The result should be a column of correctly formatted email addresses.Created All Email Addresses Using Ampersand Symbol

Read More: Create Email Address with First Initial and Last Name Using Excel Formula


Notes

The domain name remains the same for all the employees. To avoid mistakes, the cell containing the domain name should be referenced as absolute in the formula. To do this, you can use the dollar sign($) in front of the row number and column number, or simply press the F4 key.


Download Practice Workbook

You can download and practice from our workbook here.


Related Articles


<< Go Back to Address Format | Text Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo