Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. You can easily create an email address using first initial and last name in Excel. In this article, I will demonstrate 3 effective Excel formula to create email address with first Initial and last name.
Excel Formula to Create an Email Address with First Initial and Last Name: 3 Easy Methods
This is the dataset that I am going to use. I have the first and last names of some people. I will create email addresses using these names.
1. Combine LOWER, LEFT Functions to Create an Email Address with First Initial and Last Name
In this section, I will describe how to create an email address using the LOWER and LEFT functions along with Ampersand (&) symbol.
Steps:
- Go to D5 and write down the following formula
=LOWER(LEFT(B5,1))&LOWER(C5)&"@gmail.com"
Formula Breakdown:
“@gmail.com” → “” returns the text inside it.
Output: @gmail.com
LOWER(C5) → Returns the letters of C5 in lower case.
Output: leon
LEFT(B5,1) → Returns the 1st letter of B5 starting from left.
Output: J
LOWER(LEFT(B5,1)
LOWER(J)
Output: j
LOWER(LEFT(B5,1))&LOWER(C5)&”@gmail.com” → & joins the neighbouring texts.
j&leon&@gmail.com
Output: [email protected]
- Press ENTER. Excel will return the output.
- Use Fill Handle to AutoFill up to D11.
Read More: Formula to Create Email Address in Excel
2. Merge LOWER, LEFT, and CONCAT Functions to Create an Email Address with First Initial and Last Name
Now I will show another method using the LOWER and LEFT functions along with the CONCAT function.
Steps:
- Write down the following formula in D5.
=CONCAT(LOWER(LEFT(B5,1)),LOWER(C5),"@gmail.com")
Formula Breakdown:
“@gmail.com” → “” returns the text inside it.
Output: @gmail.com
LOWER(C5) → Returns the letters of C5 in lower case.
Output: leon
LEFT(B5,1) → Returns the 1st letter of B5 starting from left.
Output: J
LOWER(LEFT(B5,1))
LOWER(J)
Output: j
CONCAT(LOWER(LEFT(B5,1)),LOWER(C5),”@gmail.com”) → CONCAT function joins the neighbouring texts.
j&leon&@gmail.com
Output: [email protected]
- Now press ENTER. Excel will return the output.
- Now, use Fill Handle to AutoFill up to D11.
Read More: How to Format a Column for Email Addresses in Excel
3. Use a Combination of LOWER, LEFT, RIGHT, and FIND Functions to Create an Email Address
Here, I am going to discuss another method. Suppose the names are not separated in First Name and Last Name. In that case, you can use a combination of the LOWER, LEFT, RIGHT, and FIND functions to create an email address. This is the dataset.
Steps:
- Go to C5 and write down the following formula
=LOWER(LEFT(B5,1)&RIGHT(B5,LEN(B5)-FIND(" ",B5))&"@gmail.com")
Formula Breakdown:
“@gmail.com” → “” returns the text inside it.
Output: @gmail.com
FIND(” “,B5) → Finds the position where space is located. “ ” represents a space or gap.
Output: 4
LEN(B5) → Returns the number of characters in B5.
Output: 8
RIGHT(B5,LEN(B5)-FIND(” “,B5)) → Returns the specified number of text from the end of B5.
RIGHT(B5,4)
Output: Leon
LEFT(B5,1) → Returns the 1st letter of B5 starting from left
Output: J
LOWER(LEFT(B5,1)&RIGHT(B5,LEN(B5)-FIND(” “,B5))&”@gmail.com”) → Returns the final output.
LOWER(J&Leon&”@gmail.com”)
Output: [email protected]
- Now, press ENTER to get the output.
- Then, use the Fill Handle to AutoFill up to C11.
Read More: Extracting Email Addresses from Excel
Things to Remember
- You can use the CONCATENATE function in lieu of the CONCAT function as the latter is not available in earlier versions of Excel.
Download Practice Workbook
Download this workbook and practice while going through the article.
Conclusion
In this article, I have demonstrated 3 easy Excel formula to create email address with first Initial, and last name. I hope it helps everyone. If you have any kind of suggestions, ideas, or feedback, please feel free to comment down below.
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!
