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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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

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

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


<< Go Back to Address Format | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo