Formula to Create Email Address in Excel (2 Suitable Examples)

Nowadays, companies need to create email addresses for their employees very often. It is tiring and time-consuming to create email addresses one by one. It will be handier if we can create email addresses automatically by their names and company domain. In this article, I will demonstrate the formula to create an email address in Excel.


Download Practice Workbook

You can download and practice from our workbook here.


2 Examples to Create Email Address in Excel with Formula

Suppose, we have a dataset containing the first names and last names of the employees, and we have the company domain. Now, we want to create the email addresses as the first letter of the employee’s first name combined with the last name and the @ sign, and the domain. For example, if one’s name is Adam Smith and the company’s domain is exceldemy.com, the desired email address would be [email protected]. Go through any of the following methods to accomplish this. 👇

Dataset to Create Email Address in Excel


1. Combine CONCATENATE, LOWER, and LEFT Functions to Create Email Address

You can use the CONCATENATE function to create email addresses for your employees. In addition to this, You will need the LOWER function and the LEFT function in creating email addresses in your desired format. Follow the steps below to do this. 👇

The CONCATENATE function is used to combine multiple texts. If you want to put some value or sign in the argument, you need to put the value or sign inside double quotation marks(“ ”).

CONCATENATE Function Syntax

The LOWER function is an Excel function that lowercases a text value. It has mainly one argument only, that is text. You need to put the text value or cell reference in the argument. And, the function will return the value as lowercase text.

LOWER Function Syntax

The LEFT function is an Excel function that trims a specific number of characters of a text value. It has mainly two arguments, where one is optional. Such as text and num_chars. The text argument takes in the text which needs to be trimmed. And, the num_chars argument takes the argument for how many characters should be trimmed.

LEFT Function Syntax

📌 Steps:

  • First and foremost, click on the E5 cell, where you want your desired email address.

Select Cell to Insert Formula to Create Email Address

  • Subsequently, insert the formula below. Following, press the Enter button.
=CONCATENATE(LOWER(LEFT(B5)),LOWER(C5),"@",$D$5)

CONCATENATE Formula to Create Email Address in Excel

🔎 Formula Breakdown:

  • LEFT(B5):
    → It trims the B5 cell to one character. So, it returns only the first letter.
    Result: M
  • (LOWER(LEFT(B5)):
    → It lowercases the first letter extracted by the LEFT function.
    Result: m
  • LOWER(C5):
    → It lowercases the letters of the C5 cell.
    Result: jones
  • =CONCATENATE(LOWER(LEFT(B5)),LOWER(C5),”@”,$D$5)
    → It combines the lowercase first letter of the B5 cell, the lowercase text of the C5 cell, the ‘@’ symbol, and the D5 cell which is the domain name.
    Result: [email protected]
  • As a result, you will get the email address in your desired format for your employee Mathew Jones.
  • Now, place your cursor on the bottom-right position of the E5 cell. Consequently, you will see that a black fill handle will appear. Subsequently, drag the fill handle downward to copy the formula for all the cells below.

Copy Formula to Create All Email Addresses

Thus, you can see you have created the email addresses for all of your employees in Excel. And, for example, the result sheet will look like this. 👇

Created Email Address Using CONCATENATE Function

Note:

You can also use the CONCAT function instead of the CONCATENATE function. These two work same in this regard.

Read More: How to Format a Column for Email Addresses in Excel (2 Easy Ways)


2. Combine Ampersand (&) Symbol with LOWER and LEFT Functions to Generate Email Addresses

You can also use the Ampersand (&) symbol’s functionality to automate email addresses in Excel. Follow the steps below to achieve this. 👇

📌 Steps:

  • At the very beginning, click on the E5 cell to write the desired formula in this cell.

Select Cell where You want to Insert the Formula

  • Following, write the formula that is given below in the E5 cell. Subsequently, press the Enter button.
=LOWER(LEFT(B5)&C5)&"@"&$D$5

Formula to Create Email Address in Excel Using Ampersand Symbol

🔎 Formula Breakdown:

  • LEFT(B5):
    → This return the first letter of the B5 cell.
    Result: M
  • LOWER(LEFT(B5)&C5):
    → This combines the first letter of the B5 cell and the value of the C5 cell and lowercase the combination.
    Result: mjones
  • LOWER(LEFT(B5)&C5)&”@”&$D$5
    → This combines the previous result with the ‘@’ symbol and the domain name.
    Result: [email protected]
  • As a result, you have automated an email address for Mathew Jones.
  • At this time, put your cursor in the bottom right position of the cell. Following, when the fill handle appears, drag it down to copy the formula to all the cells below.

Drag Fill Handle to Copy Formula

Finally, you can see all your employees’ email addresses are created. Thus, the result sheet should look like this. 👇

Created All Email Addresses Using Ampersand Symbol

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


💬 Notes

  • Here, your domain name remains the same for all the employees. So, to avoid mistakes, the cell containing the domain name should be referenced as absolute in the formula. To do this, you can use the dollar sign($) in front of the row number and column number. Else, you can simply press the F4 key.

Conclusion

To conclude, in this article, I have shown you two methods to apply formulas to create email addresses in Excel. Go through the full article and subsequently practice on your own from our practice workbook. I hope you find this article helpful and informative. If you have any further queries or recommendations, feel free to comment here.

And, visit ExcelDemy to learn more about Excel functions and formulas. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo