SharePoint column validations for SSN and Email

SharePoint column validations for SSN and Email
7 votes, 4.86 avg. rating (96% score)

Introduction

In this post we will check the formulas for SharePoint column validation to implement

  • Valid SSN (Social Security Number) number
  • Valid Email

1. SharePoint validation formula for SSN (Social Security Number) number

Considering column ‘SSN’ as single line of text and length as 11 characters

Formula


=IF(Len(SSN) = 11,
AND(
MID([SSN],4,1)=”-“,
MID([SSN],7,1)=”-“,
CODE(MID([SSN],1,1))>47,
CODE(MID([SSN],1,1))<58, CODE(MID([SSN],2,1))>47,
CODE(MID([SSN],2,1))<58, CODE(MID([SSN],3,1))>47,
CODE(MID([SSN],3,1))<58, CODE(MID([SSN],5,1))>47,
CODE(MID([SSN],5,1))<58, CODE(MID([SSN],5,1))>47,
CODE(MID([SSN],5,1))<58, CODE(MID([SSN],6,1))>47,
CODE(MID([SSN],6,1))<58, CODE(MID([SSN],8,1))>47,
CODE(MID([SSN],8,1))<58, CODE(MID([SSN],9,1))>47,
CODE(MID([SSN],9,1))<58, CODE(MID([SSN],10,1))>47,
CODE(MID([SSN],10,1))<58, CODE(MID([SSN],11,1))>47,
CODE(MID([SSN],11,1))<58 ) , False)

This formula will enforce users to give proper SSN value. SSN (Social Security Number) will be of the format XXX-XX-XXXX where X is any number
Example of valid SSN number is 987-65-4320. Any other inputs like abc-65-4320 will give error message

2. SharePoint validation formula for Email

Considering column ‘Email’ as single line of text type.


Formula


=IF(ISERROR(FIND(“@”,[Email]) – FIND(“.”,[Email]) > 0),FALSE,
(LEN(LEFT([Email],FIND(“@”,[Email])-1))>0)
+(LEN(RIGHT([Email],LEN([Email])-FIND(“.”,[Email],FIND(“@”,[Email]))))>0)
+(LEN(MID([Email],FIND(“@”,[Email])+1,FIND(“.”,[Email],FIND(“@”,[Email]))-FIND(“@”,[Email])-1))>0)
+(ISERROR(FIND(” “,[Email]))=TRUE)
=4
)

This formula will enforce ‘Email’ value should be valid email
If I give adi@gmail.com as input it will allow, but if I give the value as adi@gmail , it will not allow

One point you have to observe in the formulas is
If we use a formula FIND(“@”, [Email]) will return index where ‘@’ is available in [Email] column input value.
But, if ‘@’ is not available in the input value then it will return error “The validation formula has evaluated to an error”.
So, we have to use ISERROR function so that our formula will execute only after proper inputs

Note that, these are column level validations as formula involves with column itself not with another column. So, validation rule should be provided at
List Settings > select column under columns > Add formula under Column Validation

You can refer list of ASCII codes here http://www.asciitable.com/

Conclusion

Hope this post helps you in implementing SharePoint validations

December 11, 2013 В· Adi В· No Comments
Tags: , , ,  В· Posted in: Sharepoint 2010, SharePoint 2013, Validations

Leave a Reply

What is 2 + 2 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)