SharePoint Column Validation Formula Examples

Column validation in SharePoint is a feature that allows you to define rules for the data entered into specific columns within a SharePoint list or library. These rules ensure that the data meets certain criteria or follows specific formats, helping to maintain data integrity, accuracy, and consistency.

In this tutorial, we will learn how to validate columns in SharePoint Online.

Additionally, we will discuss the topics below:

• SharePoint email column validation
• SharePoint date column validation
• SharePoint text column validation
• SharePoint column validation number of digits
• SharePoint column validation date must be greater than today
• SharePoint column validation multiple conditions
• SharePoint list/library validation
• SharePoint list validation: requiring at least one column entry

How to Validate Column in SharePoint List or Library?

In this example, we validate the phone number column in the SharePoint list.

To do this, follow the below steps:

1. Create a new column in a list or library following the instructions provided in the image below. If you already have a column, edit that column.

2. In my case, the name of the column is Phone, then expand the More options.

3. Next, scroll down and expand the ‘Column validation‘ dropdown menu.

4. Then, in the formula bar, put the formula below. Because here, I want the phone number to be in USA format like +1 (###) ###-####.

=AND(
    LEN([Phone])=17,
    LEFT([Phone], 2)="+1",
    MID([Phone], 3, 1)=" ",
    MID([Phone], 4, 1)="(",
    MID([Phone], 8, 1)=")",
    MID([Phone], 9, 1)=" ",
    MID([Phone], 13, 1)="-",
    ISNUMBER(VALUE(MID([Phone], 5, 3))),
    ISNUMBER(VALUE(MID([Phone], 10, 3))),
    ISNUMBER(VALUE(MID([Phone], 14, 4)))
)

This SharePoint column validation formula checks if a phone number is in a specific format and if it meets certain criteria:

• Check if the length of the string is 17.
• Check if the first two characters are “+1”.
• Ensures there’s a space after the country code.
• Ensures there’s an opening parenthesis after the space.
• Ensures there’s a closing parenthesis after the third digit.
• Ensures there’s a space after the closing parenthesis.
• Ensures there’s a hyphen after the sixth digit.
• Check if the characters representing the phone number are numeric.

5. Then, you must give a user message that will appear if the formula returns a false result. Here, I give: ‘Enter the Phone Number in this format +1 (###) ###-####‘. Then click Save.

6. To check, click on “+New,” type “Title” as “1” and “Phone” as “+1 (123) 123-1234,” and click “Save.”

7. Then you can see the Phone number saved in the list.

8. If you enter a phone number in any other format, an error will be displayed based on the format you entered in the user message, as shown in the screenshot below.

This way, you Validate the Phone number column in the SharePoint list or library.

SharePoint Email Column Validation

Now, let’s look at an example of how to validate an email column in a SharePoint Online list.

Here, I have created a single-line column named “Email.”

To validate the Email column, we use below formula:

=AND(
    ISERROR(FIND(" ", [Email])), 
    ISNUMBER(FIND("@", [Email])), 
    ISERROR(FIND("@", [Email], FIND("@", [Email])+1)), 
    ISNUMBER(FIND(".", [Email], FIND("@", [Email])+2)), 
    FIND(".", [Email], FIND("@", [Email])+2) < LEN([Email])
)

Where;

• ISERROR(FIND(” “, [Email])) = This line checks if there are no spaces in the email address.
• ISNUMBER(FIND(“@”, [Email])) = This line checks if the email address contains at least one “@” symbol.
• ISERROR(FIND(“@”, [Email], FIND(“@”, [Email])+1)) = This line checks if there is only one “@” symbol in the email address.
• ISNUMBER(FIND(“.”, [Email], FIND(“@”, [Email])+2)) = This line checks if there is at least one dot (“.”) after the “@” symbol.
• FIND(“.”, [Email], FIND(“@”, [Email])+2) < LEN([Email]) = This line checks if the dot (“.”) found after the “@” symbol is not the last character in the email address. This ensures that there is at least one character after the dot.

This formula checks if the Email column value matches the format of an email address, like “example@gmail.com.” If it doesn’t, it will show “Enter the valid Email ID.”

SharePoint Date Column Validation

Now, let’s see how to validate a date column in SharePoint list.

I have taken a column of “Date and time” datatype in a SharePoint list. The name of the column is “Date”. Here, I want to ensure that the entered date falls within a specific range. To do this, I’m using the following formula:

=AND(
    [Date] >= DATE(2024, 1, 1),
    [Date] <= TODAY()
)

where:

• [Date] >= DATE(2024, 1, 1) = This part of the formula checks if the date entered in the [Date] column is on or after January 1, 2024.
• [Date] <= TODAY() = This part of the formula checks if the date entered in the [Date] column is on or before the current date (today).

This way, we validate the date column in SharePoint.

SharePoint Text Column Validation

Here we will see how to validate a text column in SharePoint.

Here, I have added a single-line column named “EmployeeName.” In the “Column Validation” section, I’ve added the following rule:

=AND(
    LEN([EmployeeName]) >= 3,
    LEN([EmployeeName]) <= 50,
    ISERROR(FIND("0", [EmployeeName])),
    ISERROR(FIND("1", [EmployeeName])),
    ISERROR(FIND("2", [EmployeeName])),
    ISERROR(FIND("3", [EmployeeName])),
    ISERROR(FIND("4", [EmployeeName])),
    ISERROR(FIND("5", [EmployeeName])),
    ISERROR(FIND("6", [EmployeeName])),
    ISERROR(FIND("7", [EmployeeName])),
    ISERROR(FIND("8", [EmployeeName])),
    ISERROR(FIND("9", [EmployeeName]))
)

This formula checks if the “EmployeeName” column meets the following criteria:

• Minimum length requirement: at least 3 characters.
• Maximum length requirement: no more than 50 characters.
• No numbers (0-9) are allowed in the employee’s name.

With this formula, you can validate the name column in SharePoint.

SharePoint Column Validation Number of Digits

let’s explore an example of validating the number of digits in a SharePoint column.

The SharePoint column validation formula provided is for validating an “Age” column in a SharePoint list using the below formula:

=AND(
    [Age] >= 18,
    [Age] < 100,
    LEN(TEXT([Age], "0")) = 2
)

Where:

• [Age] >= 18 = This part of the formula checks, if the value entered in the “Age” column, is greater than or equal to 18.
• [Age] < 100 = This part of the formula ensures that the age entered is less than 100.
• LEN(TEXT([Age], “0”)) = 2 = After converting the numerical value to text, it checks if the length of the resulting text representation is exactly 2 characters.

This way, you can validate the age column in SharePoint.

SharePoint Column Validation Date Must be Greater Than Today

Now, let’s explore an example of validating a date column in SharePoint, ensuring that the date must be greater than today using SharePoint column validation.

In the SharePoint Online list with a “Due Date” column, we will implement validation to ensure that users can select a date that is greater than today’s date.

To do this, we are using below formula:

[Due Date]>Today()

Here User Message: Due date should be greater than today

If the user enters less than today, the error will show that the Due date should be greater than today.

SharePoint Column Validation Multiple Conditions

If you want to validate a SharePoint column with multiple conditions, such as allowing only specific titles like “Mr”, “Miss”, or “Mrs”, you can use a formula like this:

=OR(Salutation="Mr",Salutation="Miss",Salutation="Mrs")

Apply this formula as the validation rule for your SharePoint column, and it will ensure that only the specified titles are allowed.

SharePoint List/Library Validation

SharePoint List/Library Validation is a feature that helps ensure the data entered into your SharePoint lists or libraries is consistent with other columns in the list or library.

Scenario:

You have a SharePoint list named “Project Tasks,” where users are required to indicate whether a task is “Pre” or “Post” based on whether they selected “Yes” to a previous task attribute

Now follow the below steps to do this:

1. Go to the “Project Tasks” list. Here, create two columns:

• The first column is a Choice column named “Requires Preparation?” where users indicate whether the task requires preparation as “Yes” or “No”.

• The second column is a Choice column named “Task Type,” where users select whether the task is “Pre” or “Post.”

2. Click on the settings gear icon in the top-right corner of the page. Then select “List settings” from the dropdown menu.

3. Inside the Settings page, click Validation settings.

4. In the “Formula” text box, enter the following formula:

=IF(RequiresPreparation="No", ISBLANK(TaskType), IF(RequiresPreparation="Yes", NOT(ISBLANK(TaskType)), FALSE))

Then, in the User Message box, you can put the below text:

Please select either "Pre" or "Post" for the task type if "Requires Preparation?" is Yes.

After that, click on Save.

5. When you go to the list, if you try to add “RequiresPreparation” as “No” and “TaskType” as “Pre“, it will not save and give the error: “Please select either ‘Pre’ or ‘Post’ for the task type if ‘Requires Preparation?’ is Yes.”

By following these steps, you can Validate SharePoint List/Library.

SharePoint List Validation: Requiring at Least One Column Entry

Let’s consider a scenario where you have a SharePoint list for storing employee contact information. In this scenario, you have two columns: “Business Phone” and “Mobile Phone”.

You want to ensure that users provide at least one phone number (either business or mobile) when adding or editing an item in the list.

I hope you all created a list and columns now open list Validation settings. Put the below formula in the formula box:

=OR([Business Phone]<>"",[Mobile Phone]<>"")

You can give the User Message like below:

Please provide at least one phone number (Business Phone or Mobile Phone).

Then click on Save.

When you input information in the list but forget to add any phone number (Business Phone or Mobile Phone), you can’t save the information. It will give you an error: “Please provide at least one phone number (Business Phone or Mobile Phone)“

This way, you have set up column validation in your SharePoint list to ensure that users provide at least one phone number (either business or mobile) when adding or editing an item.

I hope this tutorial finds you well. In this tutorial, we learned how to validate columns in SharePoint Online, including column phone number validation, email validation, text column validation, column validation for the number of digits, and date validation for greater than today.

Also, we learned how to validate columns in the SharePoint list/library using various examples.

You may also like:

• Create a Board View in a SharePoint Online List
• SharePoint Online List Forms JSON Formatting
• How to Manage Alerts in SharePoint Online
• Share SharePoint Site with External Users
• SharePoint Calculated Column