Akib Bin Rashid
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
Download Practice Workbook
Download this workbook and practice while going through the article.
3 Easy Methods to Create an Email Address with First Initial and Last Name Using Excel Formula
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: How to Format a Column for Email Addresses in Excel (2 Easy Ways)
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.
Similar Readings
- How to Separate Address Number from Street Name in Excel (6 Ways)
- Split Inconsistent Address in Excel (2 Effective Ways)
- How to Separate City and State without Commas in Excel (3 Smart Ways)
- Separate City State and Zip from Address Using Excel Formula
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.
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.
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 Separate Address in Excel with Comma (3 Easy Methods)
- Format Address Labels in Excel (3 Steps)
- How to Make Address Labels in Word from Excel (With Easy Steps)
- Separate City and State in Excel (3 Effective Methods)
- How to Make an Address Book in Excel (An Ultimate Guide)
- Format Addresses in Excel (4 Easy Methods)
