Dataset Overview
Our dataset contains the first and last names of some people.
Method 1 – Combine LOWER and LEFT Functions
- Open your Excel workbook containing the dataset with first and last names.
- In cell D5, enter the following formula:
=LOWER(LEFT(B5,1))&LOWER(C5)&"@gmail.com"
Formula Breakdown:
- LEFT(B5,1) extracts the first letter of the last name (B5).
- LOWER(C5) converts the first name (C5) to lowercase.
- The & symbol concatenates the components.
- Finally, we add @gmail.com to complete the email address.
- Press ENTER. Excel will generate the output.
- Use the Fill Handle to AutoFill the formula to D11.
Read More: Formula to Create Email Address in Excel
Method 2 – Merge LOWER, LEFT, and CONCAT Functions
- In cell D5, enter the following formula:
=CONCAT(LOWER(LEFT(B5,1)),LOWER(C5),"@gmail.com")
- Similar to Method 1, this formula combines the first initial, last name, and “@gmail.com.”
- Press ENTER to get the output.
- Autofill the formula down to cell D11 using the Fill Handle.
Read More: How to Format a Column for Email Addresses in Excel
Method 3 – Handling Combined First and Last Names
Suppose your dataset doesn’t separate first and last names. In that case, you can use a combination of functions to create email addresses:
- In cell C5, enter the following formula:
=LOWER(LEFT(B5,1)&RIGHT(B5,LEN(B5)-FIND(" ",B5))&"@gmail.com")
Formula Breakdown:
- FIND(” “, B5) locates the position of the space between the names.
- LEN(B5) calculates the total length of the name.
- RIGHT(B5, LEN(B5) – FIND(” “, B5)) extracts the last name.
- The rest of the formula follows the same pattern as before.
- Press ENTER to obtain the output.
- Autofill the formula down to cell C11.
Read More: Extracting Email Addresses from Excel
Additional Note:
- If you’re using an earlier version of Excel, consider using the CONCATENATE function instead of CONCAT.
Download Practice Workbook
You can download the practice workbook from here:
Related Articles
- How to Make an Address Book in Excel
- How to Format Addresses in Excel
- How to Organize Addresses in Excel
<< Go Back to Address Format | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
