SharePoint List Validation Formula for Special Characters and string length

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 –

SharePoint List Validation Formula for Special Characters and string length

Validation Settings

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) –
=ISERROR(FIND(“,”,Title))

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 –

=AND(LEN(Title)<45,ISERROR(FIND(“,”,Title)),ISERROR(FIND(“‘”,Title)))

Validation Formula

Validation Formula

Validation Formula

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

Validation Message

Validation Message

Validation Message

4 thoughts on “SharePoint List Validation Formula for Special Characters and string length

  1. “” is the SharePoint escape notation for a double-quote; so if you want to check for one double quote you have use 4 double quotes to write FIND(“”””,Title)

  2. Hi,

    I have created two Single Line of Text columns, First column is “My ID” and Second Column is “Dept ID”, for both the columns I have given a formula and a Error Message in Users Message box,

    For “My ID” format is to insert only 9 digits, if more/less than that 9 digits than should display error message,

    Formula:

    =(LEN([My ID])=9)+(CODE(MID([My ID],1,1))>47)+(CODE(MID([My ID],1,1))47)+(CODE(MID([My ID],2,1))47)+(CODE(MID([My ID],3,1))47)+(CODE(MID([My ID],4,1))47)+(CODE(MID([My ID],5,1))47)+(CODE(MID([My ID],6,1))47)+(CODE(MID([My ID],7,1))47)+(CODE(MID([My ID],8,1))47)+(CODE(MID([My ID],9,1))47)+(CODE(MID([Dept ID],3,1))47)+(CODE(MID([Dept ID],4,1))47)+(CODE(MID([Dept ID],5,1))47)+(CODE(MID([Dept ID],6,1))47)+(CODE(MID([Dept ID],7,1))47)+(CODE(MID([Dept ID],8,1))47)+(CODE(MID([Dept ID],9,1))47)+(CODE(MID([Dept ID],10,1))<58)=19

    Error Message : "Enter Dept ID in format : AB######## "

    But I am getting error message as something different as
    "The Validation formula has evaluated to an Error"

    I need something like this
    "Only 9 digit My ID allowed " for My Id and
    "Enter Dept ID in format : AB######## " for Dept ID

    Is there anything wrong with the formula, Please help me out here. Thanks in Advance.

  3. I’m very new to SharePoint and currently doing a training project in SharePoint 2013. I need to have a controlled document. So I need to create a column which have a special character ( – ) and numbers only as shown below. I’m not sure on how to do that. Please assist.

    0942-6479
    49288-0630
    42507-072
    68788-9537
    0378-8888
    68084-181
    42914-001

    Thanks & Regards,

    Yugeswaran

Leave a Reply

Your email address will not be published. Required fields are marked *

* Copy This Password *

* Type Or Paste Password Here *