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. 👇
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(“ ”).
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.
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.
- First and foremost, click on the E5 cell, where you want your desired email address.
- Subsequently, insert the formula below. Following, press the Enter button.
🔎 Formula Breakdown:
→ It trims the B5 cell to one character. So, it returns only the first letter.
→ It lowercases the first letter extracted by the LEFT function.
→ It lowercases the letters of the C5 cell.
→ 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.
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. 👇
You can also use the CONCAT function instead of the CONCATENATE function. These two work same in this regard.
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. 👇
- At the very beginning, click on the E5 cell to write the desired formula in this cell.
- Following, write the formula that is given below in the E5 cell. Subsequently, press the Enter button.
🔎 Formula Breakdown:
→ This return the first letter of the B5 cell.
→ This combines the first letter of the B5 cell and the value of the C5 cell and lowercase the combination.
→ 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.
Finally, you can see all your employees’ email addresses are created. Thus, the result sheet should look like this. 👇
- 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.
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!
- How to Separate Address in Excel with Comma (3 Easy Methods)
- Address Labels in Excel (3 Steps)
- How to Make an Address Book in Excel (An Ultimate Guide)
- Format Addresses in Excel (4 Easy Methods)
- How to Separate Address Number from Street Name in Excel (6 Ways)
- Split Inconsistent Address in Excel (2 Effective Ways)
- Separate City State and Zip from Address Using Excel Formula