Sunday, December 9, 2012

How to validate the special character on SharePoint List Column

Step 1: Open Column Settings for which column you want to add the validations:

List Settings > Change Column > Column Validation (below Column Settings) 

Step 2: Add the following code at the Column Validation:

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

9 comments:

  1. Hi, any idea why this doesn't work for question marks?
    Thanks

    ReplyDelete
  2. or in fact full stops. It doesn't kick up a validation error and instead goes straight to the error creating the list entry.

    ReplyDelete
  3. Thank you so much for this post. Special characters interfere with workflows and this looks like a viable solution.

    ReplyDelete
  4. are you giving the correct column name. Please note that you need to enter the column Display Name instead internal name. It is working for me.

    ReplyDelete
  5. on new item validation is not working upon clicking the submit.

    It only shows on edit form. can you help me resolve this?

    ReplyDelete
  6. hi all,
    I've managed to do this, except to prevent using "
    I've found an article that says to write it with 4 quotation signs (so """"), but that doesn't seem to work either...

    Does anyone have the solution for this?

    ReplyDelete
  7. I Found out that for the apostrophes " and ' (and everything else) you can use CHAR(34) and CHAR(39). Cheers.
    eg. FIND(CHAR(34),[ColumnName])

    ReplyDelete
  8. My Development Notes: How To Validate The Special Character On Sharepoint List Column >>>>> Download Now

    >>>>> Download Full

    My Development Notes: How To Validate The Special Character On Sharepoint List Column >>>>> Download LINK

    >>>>> Download Now

    My Development Notes: How To Validate The Special Character On Sharepoint List Column >>>>> Download Full

    >>>>> Download LINK Nu

    ReplyDelete