Welcome to EMC Consulting Blogs Sign in | Join | Help

Matt Lally's Blog

SharePoint 2010 Validate Special Characters

I have a colleague who re-built my custom list based site provisioning solution using a Visual Studio Workflow where I created it using custom SharePoint Designer Workflow actions.  The solution uses the value of what an end-user puts in the “Title” field of the form to generate a URL.  Because the URL can’t contain Special Characters (i.e. ~!@#$%^&*()-+=[]';,./{}\":<>?"), I have actions which validate the column in my solution, using regular expressions, where he didn’t add that to his VS workflow redo.

My colleague left the project recently and not soon after I was alerted about an issue where the site provisioning failed for a user who put a colon and a comma in their Title – to workaround, we simply we had the user resubmit the form with instruction not to use the colon and comma in the Title field but I figured I could fix a reoccurrence by adding the validation on submission.  I didn’t want to jump into visual studio to update his form and workflow and then redeploy the whole thing so I thought “Simple solution…I’ll just add validation to the Title column in the list settings to check for special characters.”  I thought I would have found the formula out there already but had no luck so I’m putting it out now.

So here’s a formula to check for special characters in a text column which you can add via List Settings to the Validation Settings (Figure 1) for the entire list or just to the column’s Column Validation (Figure 2)

=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),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))

NOTE:  To validate double quotes, you need to enter double double quotes in your formula (highlighted above).  This sample formula is validating a single word field (i.e., Title) so you don’t need to enclose the field name in brackets ([ ])  where if the field name contains two words you do need the brackets (e.g. [Project Name]).  Also, in SharePoint 2010, the validation formulas can be very similar to data validation formulas used in the past for Microsoft Excel so if your struggling to find a decent sample for a formula search for Microsoft Excel formula examples.

List Settings Validation Options Screenshots:

image

Figure 1 (For overall list – using this method, the validation message appears at top of list item edit form upon failure).

image

Figure 2 (For only the column – using this method, validation message appears below the field in the list item edit form upon failure).

Published Friday, January 06, 2012 4:40 PM by matt.lally

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Thangu said:

Cool..Much better solution than using jQuery!

February 2, 2012 10:25 AM
 

ben said:

I get a syntax error, if I copy your example =/

June 11, 2012 11:48 AM
 

JBF said:

Hello,

There is and error for me can you write the formula for one character to debug.

Thanks

December 13, 2012 5:45 PM
 

Mysonemo said:

TO ALL,

If you have and error its because your farm have a language pack.

Replace all , by ; (Except the first check to find "," ). ITS WORKS for ME !!

Source:

http://blog.bugrapostaci.com/2011/04/14/sharepoint-calculated-columns-formula-syntax-changes-by-specific-culture/

Regards Mysonemo

December 14, 2012 10:37 AM
 

Jake said:

The best solution I have seen out there for this problem!  Thank you very much.

January 24, 2013 3:50 PM
 

Dave said:

You solved this problem for me instantly. (I had to shorten name of my list field so the whole formula would fit in the validation field; other than that, no issues.) Can't thank you enough for posting this.

March 28, 2013 10:26 PM
 

Ben said:

this great suggestion doesn't work as it passes the maximum of characters allowed in the validation field in SP 2010 :(

May 3, 2013 11:15 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit
Powered by Community Server (Personal Edition), by Telligent Systems