Monday, September 23, 2013

Email / Phone validation in SharePoint 2010 Column validation

=(LEN(LEFT([Email Address],FIND("@",[Email Address])-1))>0)+(LEN(RIGHT([Email Address],LEN([Email Address])-FIND(".",[Email Address],FIND("@",[Email Address]))))>0)+(LEN(MID([Email Address],FIND("@",[Email Address])+1,FIND(".",[Email Address],FIND("@",[Email Address]))-FIND("@",[Email Address])-1))>0)+(ISERROR(FIND(" ",[Email Address]))=TRUE)=4

--------------------
Phone Validation (example: 123-456-7896x486):

=AND(LEN([Phone Number])=16,IF(ISERROR(FIND("",[Phone Number],1)),FALSE,(FIND("",[Phone Number])=1)),IF(ISERROR(FIND("-",[Phone Number],4)),FALSE,(FIND("-",[Phone Number],4)=4)),IF(ISERROR(FIND("-",[Phone Number],8)),FALSE,(FIND("-",[Phone Number],8)=8)),IF(ISERROR(FIND("x",[Phone Number],13)),FALSE,(FIND("x",[Phone Number],13)=13)),IF(ISERROR(1*CONCATENATE(MID([Phone Number],1,3),MID([Phone Number],5,3),MID([Phone Number],9,4),MID([Phone Number],14,3))),FALSE,AND(1*CONCATENATE(MID([Phone Number],1,3),MID([Phone Number],5,3),MID([Phone Number],9,4))>1000000000,1*MID([Phone Number],1,3)<>911,1*MID([Phone Number],5,3)<>911,1*MID([Phone Number],5,3)<>555,1*MID([Phone Number],14,3)<>911)))

No comments:

Post a Comment