SharePoint column validations for SSN and Email

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/