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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Format a Column in Excel for Email Addresses: 2 Easy Ways

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.

STEPS:

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

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

  • Secondly, navigate to the Data tab and select the ‘Data Validation’ icon. It will open the Data Validation window.

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

  • In the Data Validation window, select Custom in the Allow field.
  • Type the formula below in the Formula field:
=ISNUMBER(MATCH("*@*.?*",E5,0))

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

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:
=AND(ISERROR(FIND(" ",E5)),LEN(E5)-LEN(SUBSTITUTE(E5,"@",""))=1,IFERROR(SEARCH("@",E5)<SEARCH(".",E5,SEARCH("@",E5)),0),ISERROR(FIND(",",E5)),NOT(IFERROR(SEARCH(".",E5,SEARCH("@",E5))-SEARCH("@",E5),0)=1),LEFT(E5,1)<>".",RIGHT(E5,1)<>".")
  • Here, Cell E5 is the first cell of the email address column.

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

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.

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

  • Also, check ‘Show error alert after invalid data is entered’ in the Error Alert field.
  • Click OK to proceed.

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

  • In the following step, select Cell E5 and type an email address.

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

  • Then, press Enter and you will see the formatted email address.

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

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

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

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

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

  • After pressing Enter, a message will appear saying that the value doesn’t match the data validation restrictions.

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

  • Finally, after inserting all the emails, the address column will look like the picture below.

Format Column in Excel for Email Addresses with ‘Data Validation’ Feature

Read More: Format Addresses in Excel


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.

STEPS:

  • In the first place, select Cell E5 and type the formula:
=LOWER(LEFT(B5)&C5&D5)&"@"&"xyz.com"

How Does the Formula Work?

  • The & operator connects different cell values.
  • LEFT(B5)&C5&D5

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

  • LOWER(LEFT(B5)&C5&D5)&”@”&”xyz.com”

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.

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


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.

STEPS:

  • Firstly, select Cell E5 and type the formula:
=CONCATENATE(LEFT(B5,1),C5,D5,"@axy.com")

How Does the Formula Work?

  • LEFT(B5,1)

This part extracts the leftmost character of Cell B5. So the output of this part becomes M.

  • CONCATENATE(LEFT(B5,1),C5,D5,”@axy.com”)

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.

Read More: Formula to Create Email Address in Excel


Download Practice Book

Download the practice book from here.


Conclusion

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. Last of all, if you have any suggestions or queries, feel free to ask in the comment section 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.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

2 Comments
  1. I tried your first suggestion for entering email addresses as I have tried others. However, when I enter the email addresses and then click on them, it opens a Microsoft Edge window instead of an email program. The other formulas that I have tried do the same thing. I would appreciate your help. Thanks, Russ

    • Hello Russell,
      Thank you for sharing your query. I hope the following solution will work for you.
      First, click on the Start icon and select Settings.

      Then, select Apps in the new window.

      Next, go to the Default apps section on the left panel.
      Here, check if the application for Email is selected as Mail.
      If not, then click on it and choose Mail from the list of applications.

      After this, I recommended you restart your device once. I hope it will solve your problem. Please let me know your feedback.
      Thanks!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo