Create Email Address with First Initial and Last Name Using Excel Formula

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.

excel formula create email address first initial last name


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.

excel formula create email address first initial last name

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")

excel formula create email address first initial last name

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.

excel formula create email address first initial last name


Similar Readings


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")

excel formula create email address first initial last name

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.

excel formula create email address first initial last name


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

Akib
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo