Validate special characters in SharePoint list column

 572 total views,  10 views today

Sometimes, we need to validate the special characters in SharePoint list columns – so that we can prevent the user to enter special characters in the SharePoint column while entering the data.

It is very simple to validate with just a few clicks and with the validation formula.

Step 1:

Go to the List Settings page, then click on the column for which you want to add the validation formula. For example, here I have selected the column as “Title“.

 

 

Step 2:

Click on the column name for which you want to add the validation formula – here I have clicked on the “Title” column which will take users to the “Edit Column” page.

Go to the “Column Validation” section and in the “Formula” box add the below formula and in the “User message” box, you can give your validation message which is optional but better to provide.

Syntax:

=AND(IF(ISERROR(FIND(",",[ColumnName])),TRUE),
IF(ISERROR(FIND("&",[ColumnName])),TRUE),
IF(ISERROR(FIND(";",[ColumnName])),TRUE),
IF(ISERROR(FIND("[",[ColumnName])),TRUE),
IF(ISERROR(FIND("+",[ColumnName])),TRUE),
IF(ISERROR(FIND(":",[ColumnName])),TRUE),
IF(ISERROR(FIND(")",[ColumnName])),TRUE),
IF(ISERROR(FIND("-",[ColumnName])),TRUE),
IF(ISERROR(FIND("*",[ColumnName])),TRUE),
IF(ISERROR(FIND("(",[ColumnName])),TRUE),
IF(ISERROR(FIND("$",[ColumnName])),TRUE),
IF(ISERROR(FIND("%",[ColumnName])),TRUE),
IF(ISERROR(FIND("~",[ColumnName])),TRUE),
IF(ISERROR(FIND("#",[ColumnName])),TRUE),
IF(ISERROR(FIND("]",[ColumnName])),TRUE),
IF(ISERROR(FIND(".",[ColumnName])),TRUE),
IF(ISERROR(FIND("!",[ColumnName])),TRUE),
IF(ISERROR(FIND("@",[ColumnName])),TRUE),
IF(ISERROR(FIND("/",[ColumnName])),TRUE),
IF(ISERROR(FIND("\",[ColumnName])),TRUE))

Notes:

• In the above formula, replace “ColumnName” with your column name.
• You can add or remove validation from the above formula based on your requirement.
• For the addition of a new validation condition, you can add the special character like below and include this line to the above formula.

IF(ISERROR(FIND("speacial character you want to add",[ColumnName])),TRUE),

• For the deletion of the existing validation condition, just we need to remove the “IF” condition line from the above-consolidated formula.

Example:

I have applied the validation formula in the “Title” column.

 

 

Formula added:

=AND(IF(ISERROR(FIND(",",[Title])),TRUE),
IF(ISERROR(FIND("&",[Title])),TRUE),
IF(ISERROR(FIND(";",[Title])),TRUE),
IF(ISERROR(FIND("[",[Title])),TRUE),
IF(ISERROR(FIND("+",[Title])),TRUE),
IF(ISERROR(FIND(":",[Title])),TRUE),
IF(ISERROR(FIND(")",[Title])),TRUE),
IF(ISERROR(FIND("-",[Title])),TRUE),
IF(ISERROR(FIND("*",[Title])),TRUE),
IF(ISERROR(FIND("(",[Title])),TRUE),
IF(ISERROR(FIND("$",[Title])),TRUE),
IF(ISERROR(FIND("%",[Title])),TRUE),
IF(ISERROR(FIND("~",[Title])),TRUE),
IF(ISERROR(FIND("#",[Title])),TRUE),
IF(ISERROR(FIND("]",[Title])),TRUE),
IF(ISERROR(FIND(".",[Title])),TRUE),
IF(ISERROR(FIND("!",[Title])),TRUE),
IF(ISERROR(FIND("@",[Title])),TRUE),
IF(ISERROR(FIND("/",[Title])),TRUE),
IF(ISERROR(FIND("\",[Title])),TRUE))

Test – Demo:

After adding the validation formula to the “Title” column when we try to enter an item with special character in the “Title” column, we will not be able to save the data and user will get the validation message “Please enter the valid data”.

In the above example, we have entered the “Test . Item” where dot “.”  is the special character as per the above validation formula which we have applied, so it does not allow to save the data, however, once we remove the dot “.” from the “Title” column value, it will be saved successfully, please see the below:

 

Summary:

Thus, in this blog, we have learned about how to validate the special characters in the SharePoint list column and prevent being entered to the list item.

See Also:

• Getting Error “InfoPath cannot generate a form template for the SharePoint list”
• How to auto populate field in InfoPath based on another field
• Add more than 5 conditions in InfoPath form’s rule
• How to validate the date column in Infopath form
• How to a copy list item to another list using SharePoint designer workflow
• Edit user Permission is greyed Out SharePoint Online
• Can not upload SharePoint App to SharePoint App Catalog
• Column header formatting in SharePoint list Quick Edit or Datasheet View
• Enable and configure information rights management (IRM) in SharePoint Online
• 25 quick checklists for SharePoint migration
• Create app catalog site in SharePoint online step by step
• Manage recycle bin in SharePoint Online – Office 365