# Formula to Create Email Address in Excel (2 Methods)

Suppose you have the following dataset.

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

Steps:

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

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

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.

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

Note:

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

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

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

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.

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

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

### Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF