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.
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).
- 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.
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
- How to Format Addresses in Excel
- How to Organize Addresses in Excel
- Extracting Email Addresses from Excel
- How to Format a Column for Email Addresses in Excel
<< Go Back to Address Format | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
