SharePoint Column Validation [10 real examples]

In this SharePoint tutorial, we will see a few examples of SharePoint column validation. This sharepoint list column validations will work in SharePoint Online as well as SharePoint on-premises versions.

In this SharePoint column validation example, we will discuss how to add validations to the SharePoint column and where we add the column validation rule in SharePoint 2013/2016/Online. Here we will discuss how to add the validation in the SharePoint custom list column.

Validations are very much necessary in SharePoint 2013/2016/Online list columns.

Here I have a SharePoint Online list as “StudentInformation” and I will show you how to add column validations in SharePoint Online/2013/2016/2019 list.

SharePoint column validation examples

Here we will see a few SharePoint column validation examples:

• SharePoint email column validation
• SharePoint phone number column validation
• SharePoint date column validation
• SharePoint text column validation
• SharePoint column validation number of digits

1. SharePoint email column validation

Now, we will see an example on SharePoint email column validation.

For the mail id validation, I have created one single line of a column named “StudentEmailId”. When You are creating the “StudentEmailId” column in the create column page scroll down the page we can able to see the “Column Validation” option.

Click on the “Column Validation” option, we can able to see two fields one for formulas and one is user message.

Here I want to set the validation for “StudentEmailId” column. When the user entered the wrong email id then the user message will display as a warning message.

In the formula field, I have added the below formula

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

In the User message field, I have added the message “Enter The Valid Email Id”. When a user goes to add a new item to the list and enters a valid email id in the email id field it will save the data to the list. The User message will display if the Email Id is invalid.

Read SharePoint Online List Forms JSON Formatting

2. SharePoint phone number column validation

Now, we will see an example of SharePoint phone number column validation.

I have created one column named “PhoneNumber” in the SharePoint list and in the Formula field of Column validation I have added the below formula.

=AND(LEN(PhoneNumber)=14,IF(ISERROR(FIND("+",PhoneNumber,1)),FALSE,(FIND("+",PhoneNumber)=1)),IF(ISERROR(FIND("-",PhoneNumber,4)),FALSE,(FIND("-",PhoneNumber,4)=4)),IF(ISERROR(1*CONCATENATE(MID(PhoneNumber,2,2),MID(PhoneNumber,5,10))),FALSE,TRUE))

In the User message, I have added the message “Enter the valid mobile number”.

According to the formula if the user gives the mobile number in the below format then only the field will take the mobile number.

Ex:+91-**

I have created a new item in the phone number field I have given the number as “+91-**” So the number is stored to the SharePoint list.

Read SharePoint list View Tutorial

3. SharePoint date column validation

Now, we will see how to an example on SharePoint date column validation.

Here we will see the Date validation. I have taken one column of “date and Time’ datatype in SharePoint custom List and given the list name as “StartDate”, see the above screenshot. In the column validation, I have added the formula

=StartDate>TODAY()//Start date should be more than today

In the user message, I have added the message “Start date should be greater than today”.

4. SharePoint text column validation

Now, we will see an example on SharePoint text column validation.

In the “StudentInformation” SharePoint custom List for checking the Name validation, I have Added one Single line of a column named the column name as “Fullname” and in the Column validation in the rule field I have added the rule

=OR(LEN(Fullname)=9,LEN(Fullname)=12)

I want The length of Fullname should be 9 or 12. In the user message, I have added the message “Enter the 9 letter name or 12 letters”.

Read Delete all items from a SharePoint Online list using PnP PowerShell

5. SharePoint column validation number of digits

Now, we will see an example of the SharePoint column validation number of digits.

Here I want to put the validation that when the user enters the number then the length of the number should be more than 5. So I have created a “StudentRN” column in which data type is a single line of text.

In the Column, validation writes the below formula:

=IF(LEN(StudentRN)>5,TRUE,FALSE)

and in the “User message” field put the text. Here I have added the text “Enter The number which range is more than 5” in the User message Field.

When We want to Add a new item to the list and without obeying the validation condition and Click on Save it is showing all the “User message” below the field.

Read SharePoint list view threshold

6. Allow particular value to a SharePoint List Column

In the first example, we will see how to allow a particular value in the SharePoint list column, else it will show a validation error.

Here we can see how a list column validation works in SharePoint Online/2013/2016.

I have a SharePoint 2013 list that contains a Title column, I want users should be able to put only “Mr” or “Mrs”. If users put something else other than this, then it should give an error message saying You can put only Mr or Mrs.

To use validation for a particular column, follow the below steps:

Open your SharePoint List, then go to the List Settings page. In the Settings page, go to the Columns section and then click on the particular column for which you want to use the validation. In my case, it is the Title Column.

This will open the Edit Column page, here click on “Column Validation“. Then in the Formula box put like below:

=OR(Title="Mr",Title="Mrs")

And in the User message, put the message which the user wants to see if the validation fails like: “Please only put Mr or Mrs”.

The formula should look like below:

Then click on OK and Save the formula.

Now try to add one item to the SharePoint list. If you put any other thing apart from “Mr” or “Mrs” in the Title column, it should display the message like below:

Read SharePoint list title column

7. SharePoint List Column Age Validation

Now, in this example, we will see how we can validate Age using SharePoint list column validation.

If you want to validate another column, let’s say you have an age column and you want to show a validation message if user put value less than 25.

You can write in the Column validation formula like below:

=(Age>25)

It should look like this:

Now when a user tries to put value less than 25, it will show a message like below:

Read SharePoint Create Folder in List or Document Library

8. SharePoint List Column Validation (Require at least one column validation)

Now, we will see how we can implement “Require at least one column validation” in a SharePoint list column.

Here I have a Contact list in my SharePoint Online site. In that list, 3 columns are there like:

• Home Phone
• Business Phone
• Mobile Phone

The requirement here is that a user has to enter at least one phone number.

Here we can use list validation. To implement list validation, follow below steps.

Go to the SharePoint list settings page and click on “Validation settings” which are under General Settings like below:

Then in the Validation Settings page write the formula like below:

=COUNTA([Home Phone],[Business Phone],[Mobile Number])>=1

And you can also put the error message in the “User Message” section like below:

Click on Save, to save the formula.

Now, when a user tries to submit without giving a phone number, the validation will trigger like below:

Similarly, if you want to validate other scenarios like the user should enter either mobile number or email id, then you can write a formula like below:

=COUNTA([Emil ID],[Mobile Number])>=1

You can check out a few examples of common formulas in SharePoint Lists here.

Read How to display SharePoint list data in an HTML table using JavaScript

9. SharePoint column validation date must be greater than today

Now, we will see the SharePoint column validate date example, we will see how we can validate that date must be greater than today using SharePoint column validation.

Here I have a SharePoint Online list which has a Due Date column, here we will implement validation to the date column so that user can select date must be greater than today.

Open the SharePoint Online list, Go to the List Settings page. And then choose Validation settings which is under General Settings.

Then in the Validation Settings page, Add the below formula and a user message like below:

Formula: [Due Date]>Today()

User Message: Due date should be greater than today

Now, when a user selects a date that is less than today, then you can see an error message like the below:

Hope this example, helps to “SharePoint column validation date must be greater than today”.

Similarly, if you want to check the Due Date should be greater than the created date, then you can write the formula below:

=[Due Date] > [Created]

Read How to change column order in SharePoint List new form

10. SharePoint column validation: Start date and end date validation (AND clause in List Validation)

Now, we will see how we can do SharePoint column validation for start date and end date. In this particular example, we will see how use should enter due date should be greater than today and less than 30days.

We will also see how we can use AND clause in list validation in SharePoint.

Open the SharePoint Online list, Go to the List Settings page. And then choose Validation settings which is under General Settings.

Then in the Validation Settings page, Add the below formula and a user message like below:

=IF([Due Date]>Today(),IF(DATEDIF(Today(),[Due Date],"d")<=90,TRUE,FALSE),FALSE)

Now, if a user enters Due Date which is greater than 30 days then you can see an error like the below:

In this SharePoint article, we discussed how to add validation in the list column in SharePoint Online/2013/2016/2019.

Here we see a few SharePoint column validation examples:

• SharePoint email column validation
• SharePoint phone number column validation
• SharePoint date column validation
• SharePoint text column validation
• SharePoint column validation number of digits

You may like the following SharePoint Online tutorials:

• SharePoint Online copy list items to another list programmatically
• How to add Comments to SharePoint Online List Items and Microsoft 365 Lists Items
• Microsoft Lists – Create a List from Excel
• Microsoft Lists Formatting Examples
• SharePoint alerts – Create and Manager Alerts in List or Library
• Disable attachments in SharePoint list