SharePoint List Validation Formula for Special Characters and string length
Let me start off by saying I don’t recall ever using the SharePoint out of the box list validation, but yesterday when I needed to I searched the web and was surprised to see there aren’t many examples and best practices for using it out there, so I thought sharing this might be nice.
First of all in order to add a validation rule go to your SharePoint list or library settings and click on Validation settings –
Secondly it’s confusing and important to understand that in order for your validation rule to work (meaning a message will be displayed for the user and the form won’t be submitted) your formula must return ‘TRUE’.
Here are the formulas that I recently used:
A validation formula for the number of string characters (Useful for the OOTB Title column for which you cannot limit the number of characters) –
=LEN(Title) < 45
A validation formula that checks for special characters (e.g. if the title contains a comma) –
Important Note: The ISERROR syntax is used here like a “NOT” rule.
And now for combining a few validation rules together – in the following formula if the title is longer than 45 characters OR contains a comma OR contains an apostrophe, the validation message will be displayed and the form will not be submitted –
Important note: It’s confusing but we must use “AND” for combining validation rules if we want our validation to work in case one or another of them is violated, that’s because if the entire expression will return a “FALSE” value our validation won’t work… again like I said it’s a bit confusing, hope you get it