2 ways email validation in SharePoint list column

2 ways email validation in SharePoint list column

No comments

Loading

In this “Email validation in SharePoint list column” article, we will learn at how to validate an email ID in a single-line text column in SharePoint. And also, we will learn how to work with SharePoint column validation from the list settings page. This will work both in SharePoint on-premises and SharePoint Online list columns.

2 ways email validation in SharePoint list column

We can validate email addresses in a SharePoint list column using the column validation formula configuration. A few changes will be made to the required and optional columns.

Use case 1: Email validation

Here we will validate the email format using the SharePoint list column validation formulas.

Email validation in SharePoint list column (with optional column)

Let’s create a single-line text column called “CustomerEmail.” Make the column optional, which means don’t select yes in the “Require that this column contain information” section.

Then, in the column validation box, enter the below validation:


=IF(LEN(CustomerEmail)<=0,TRUE,AND(ISERROR(FIND(" ",CustomerEmail,1)),IF(ISERROR(FIND("@",CustomerEmail,2)),FALSE,AND(ISERROR(FIND("@",CustomerEmail,FIND("@",CustomerEmail,2)+1)),IF(ISERROR(FIND(".",CustomerEmail,FIND("@",CustomerEmail,2)+2)),FALSE,FIND(".",CustomerEmail,FIND("@",CustomerEmail,2)+2)<LEN(CustomerEmail))))))

Column validation in SharePoint Online
Column validation in SharePoint Online

Notes:

  • In the above validation formula, we have added a column with a single line of text type named “CustomerEmail.”  You need to replace this column with yours.
  • In the user message box, give a user-friendly message for the validation input.

Now, if we try to pass an invalid email, we will get an error message like “Please enter a valid email address.” Example: myemail@domain.com

Email validation error message in SharePoint Online list column
Email validation error message in SharePoint Online list column

Email validation in SharePoint list column (mandatory column)

For the mandatory column email validation, it would be a little different; here is the validation formula for this.


=AND(ISERROR(FIND(" ",CustomerEmail,1)),IF(ISERROR(FIND("@",CustomerEmail,2)),FALSE,AND(ISERROR(FIND("@",CustomerEmail,FIND("@",CustomerEmail,2)+1)),IF(ISERROR(FIND(".",CustomerEmail,FIND("@",CustomerEmail,2)+2)),FALSE,FIND(".",CustomerEmail,FIND("@",CustomerEmail,2)+2)<LEN(CustomerEmail)))))

 

Email validation in SharePoint list column – Applies To

The above email validation is supported in the following versions of SharePoint:

  • SharePoint 2013
  • SharePoint 2016
  • SharePoint 2019
  • SharePoint Online

Use case 2: SharePoint list validation (SharePoint column validation  – Project Status list)

You have a project list where you have the below columns:

  • Project Name – Single line of Text column
  • Project Status – Choice type column with the values In Progress or Completed
  • Project Start Date – Date and Time type column
  • Project Completed Date – Date and Time type column

Notes:

  • From above, except for project name, all columns are optional.

The use case of the requirement could be that when you select the project status as completed, then the project completion date should be mandatory; it shouldn’t be empty, and this date should be greater or later than the project start date.

For this validation, we cannot use the column-level validation, as it involves more than one column in the validation formula (Project Completed Date, Project Start Date, and Project Status). In this scenario, we need to apply the validation in the SharePoint list settings page.

SharePoint column validation from list settings page

For this list validation, go to your list settings page.

SharePoint list validation - conditionally make column as required.

SharePoint list validation – conditionally make column as required.Click on the “Validation settings” link.

Add the below formula in the formula box and provide user friendly message for this formula or validation.

SharePoint list validation formula
SharePoint list validation formula

The formula for the above SharePoint column validation is given below as text:


=IF(AND(ProjectCompletedDate-ProjectStartDate<0,ProjectStatus="Completed"),FALSE,TRUE)

The user message for the above SharePoint column validation is given below as text:

“The project completion date is mandatory when the project is completed, and it should be later than the project start date.”

Notes:

  • In the above formula, replace these columns with yours.
  • The value in the formula box is case-sensitive, so we need to pass the exact values from the list column.

Demo: SharePoint column validation (Project completion date, start date and status)

Here we will demo the above use-case scenarios:

Demo scenario 1: When the project status is completed but the project completion date is empty and we try to save the details, we get the SharePoint column validation error as below:

Demo scenario 1 - SharePoint column validation error
Demo scenario 1 – SharePoint column validation error

Demo scenario 2: When the project status is “completed,” but the project completion date is earlier than the project start date, and we try to save the details, we get the SharePoint column validation error as below:

Demo scenario 2 - SharePoint column validation error
Demo scenario 2 – SharePoint column validation error

Summary: Email validation in SharePoint list column

Thus, in this blog, we have learned quickly how to validate the email ID in the SharePoint list column so that the user can enter a valid email ID while working on the SharePoint list form. And also, we have learned how to validate SharePoint list columns from the list settings page with date column and choice column values.

See Also: SharePoint validation formulas

You may also like the other types of validation in SharePoint and Power Apps:

If you would like to appreciate our efforts, please like our post and share it with your colleagues and friends. You may join the email list; it won’t spam you; it’s just notifications of new posts coming in, nothing else. 🙂

Loading

About Post Author

Do you have a better solution or question on this topic? Please leave a comment