How to Create an Email Address with First Initial and Last Name Using Excel Formulas (3 Methods)

Our dataset contains the first and last names of some people.

excel formula create email address first initial last name


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.

excel formula create email address first initial last name

Read More: Formula to Create Email Address in Excel


Method 2 – Merge LOWER, LEFT, and CONCAT Functions:

  • In cell D5, use the following formula:
=CONCAT(LOWER(LEFT(B5,1)),LOWER(C5),"@gmail.com")

excel formula create email address first initial last name

  • 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.

excel formula create email address first initial last name

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

excel formula create email address first initial last name

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.

excel formula create email address first initial last name

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


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

Get FREE Advanced Excel Exercises with Solutions!
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