In this article, we will learn to format a column for email addresses in Excel. In Microsoft Excel, we often insert email addresses for various purposes. Sometimes, we need to format a column for email addresses. This formation makes our work easier. And if we enter any wrong form of email then, it will show an error message. Today, we will demonstrate 2 easy methods. So, without any delay, let’s start the discussion.
Download Practice Book
Download the practice book from here.
2 Easy Ways to Format a Column for Email Addresses in Excel
To explain the methods, we will use a dataset that contains information about some employees of a company. Here, we have their First & Last Name and their IDs. We will try to format Column E for email addresses.
1. Format Column in Excel for Email Addresses with ‘Data Validation’ Feature
In the first method, we will use the ‘Data Validation’ feature to format a column in Excel for email addresses. The steps of this process are simple. Here, we will use a formula inside the ‘Data Validation’ feature and that will help us to format the column easily. So, let’s follow the steps below to see how we can format a column for email addresses.
- First of all, select cells that you want to format for email addresses.
- We will format Cells E5 to E9 of Column E.
- You can select the whole Column E if you want.
- Secondly, navigate to the Data tab and select the ‘Data Validation’ icon. It will open the Data Validation window.
- In the Data Validation window, select Custom in the Allow field.
- Type the formula below in the Formula field:
Note: The first cell of the email address column is Cell E5. In your case, it can be different. So, you need to change it according to your dataset.
- Alternatively, you can also use the formula below in the Formula field instead of the previous one:
- Here, Cell E5 is the first cell of the email address column.
This formula ensures seven conditions. They are:
- It ensures there is no space.
- Also, ensure the @ sign.
- It ensures that there is a dot (.) symbol after the @ sign.
- Also ensures that there is no comma.
- It makes sure that the dot (.) symbol is not just right after the @ sign.
- Again, it checks that the email does not start with a dot (.) symbol.
- Or ends with a dot (.) symbol.
- After inserting the formula, click ‘Show input message when cell is selected’ from the Input Message tab.
- Also, check ‘Show error alert after invalid data is entered’ in the Error Alert field.
- Click OK to proceed.
- In the following step, select Cell E5 and type an email address.
- Then, press Enter and you will see the formatted email address.
- Repeat the previous step while inserting email addresses one by one.
- If all email addresses follow the same pattern then you can take a glance at Method-2.
- Now, if you try to enter the wrong email address then it will show a message.
- In our case, we entered an email without the .com part and pressed Enter.
- After pressing Enter, a message will appear saying that the value doesn’t match the data validation restrictions.
- Finally, after inserting all the emails, the address column will look like the picture below.
2. Apply Excel Formulas to Format a Column for Email Addresses
Sometimes, in a company, all the employees get the same pattern email addresses. In those cases, you can use the following combination of functions to create the email addresses in a column quickly. Again, we will use the same dataset here.
2.1 Combine LOWER and LEFT Functions
We can combine the LOWER and LEFT functions to form a formula that will create the email addresses quickly. The LOWER function transforms all the letters into lowercase and the LEFT function extracts the leftmost characters from a given string. Let’s observe the steps below to see how this method works.
- In the first place, select Cell E5 and type the formula:
How Does the Formula Work?
- The & operator connects different cell values.
This part extracts the leftmost character of Cell B5 and concatenates it with Cell C5, D5, @, and xyz.com. So the output of this part becomes [email protected].
Here, the LOWER function converts the characters into lowercase. So, the final output is [email protected].
- After that, hit Enter and drag the Fill Handle down.
- Finally, you will get results like the screenshot below.
2.2 Apply CONCATENATE and LEFT Functions Together
We can also use the CONCATENATE and LEFT functions together to perform the same task. The CONCATENATE function concatenates the values of different cells in a single cell. The difference between this formula and the previous one is that it does not convert the strings to lowercase.
Let’s observe the steps below to know more.
- Firstly, select Cell E5 and type the formula:
How Does the Formula Work?
This part extracts the leftmost character of Cell B5. So the output of this part becomes M.
Here, the CONCATENATE function joins text strings of Cell B5, C5, and D5 along with @axy.com. So, the final output is [email protected].
- In the following step, press Enter and drag down the Fill Handle.
- Lastly, you will be able to format a column in excel for email addresses like the picture below.
In this article, we have demonstrated 2 easy ways to Format a Column in Excel for Email Addresses. I hope this article will help you to perform your tasks easily. Moreover, using Method-2 you can also generate email addresses in Excel. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. In order to read more articles like this, please visit the ExcelDemy website. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
- How to Separate Address in Excel with Comma (3 Easy Methods)
- Format Address Labels in Excel (3 Steps)
- How to Separate City and State in Excel (3 Effective Methods)
- Make an Address Book in Excel (An Ultimate Guide)
- How to Format Addresses in Excel (4 Easy Methods)
- Separate Address Number from Street Name in Excel (6 Ways)
- How to Split Inconsistent Address in Excel (2 Effective Ways)