Posted on

Prevent Special Characters in Single line text field

One of the data validation challenges is prevent the users from entering special characters in text field (Single line of text) . In this validation formula, nested if & isError will be used as in the following formula

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

Also, the same formula can be used to prevent numeric values to be entered in the text field and replacing the characters with the numbers.

I hope this will be helpful for the SharePoint developers.

Enjoy single line of text without special characters

Posted on

E-mail Validation

SharePoint developers are facing many challenges in the data validations. One of these challenges is validating the email address. Emails are stored in SharePoint column with data type single line text and this will allow the users to enter any character series with maximum number 255 which will require data validation.

  1. Validate if the e-mail column includes . & @

=AND(IF(FIND(“@”,Email,2)>0,if(FIND(“.”,Email,2)>0,true,false),false)

2. Validate if the e-mail column includes . & @ and @ comes after . at least by 2 characters

=AND(IF(FIND(“@”,Email,2)>0,if(FIND(“.”,Email,FIND(“@”,Email,2)+2)>0,true,false),false)

3. Validate if the e-mail column includes . & @ and @ comes after . at least by 2 characters. Also, there is at least 2 characters after @

=AND(IF(FIND(“@”,Email,2)>0,if(FIND(“.”,Email,FIND(“@”,Email,2)+2)>0,if(FIND(“.”,Email,FIND(“@”,Email,2)+2)<len(Email),true,false),false),false)

Enjoy a valid email data entry.