Forum Discussion

EKinz33's avatar
EKinz33
Copper Contributor
Aug 11, 2021

Column Validation for SharePoint list that combines two formulas

I have a SharePoint list where a phone number entry needs data validation. I am new at validation formulas and cannot figure out how to combine the two. I want to confirm that the number is a valid phone number, not a toll-free number or a fake number. I also want the user to be able to submit the form with nothing at all in this field (in the instance they do not have a phone number to update. I have these two formulas I want to be combined:

=AND(

    LEN([New Phone Number])=14,

    IF(ISERROR(FIND("(", [New Phone Number],1)),

        FALSE,

        (FIND("(", [New Phone Number]) = 1)

    ),

    IF(ISERROR(FIND(")", [New Phone Number],5)),

        FALSE,

        (FIND(")", [New Phone Number], 5) = 5)

    ),

    IF(ISERROR(FIND(" ", [New Phone Number],6)),

        FALSE,

        (FIND(" ", [New Phone Number], 6) = 6)

    ),

    IF(ISERROR(FIND("-", [New Phone Number],10)),

        FALSE,

        (FIND("-", [New Phone Number], 10) = 10)

    ),

    IF(ISERROR(1*CONCATENATE(MID([New Phone Number], 2, 3), MID([New Phone Number], 7, 3), MID([New Phone Number], 11, 4))),

        FALSE,

        AND(

            1*CONCATENATE(MID([New Phone Number], 2, 3), MID([New Phone Number], 7, 3), MID([New Phone Number], 11, 4)) > 1000000000,

            1*MID([New Phone Number], 2, 3) <> 888,

            1*MID([New Phone Number], 7, 3) <> 800,

            1*MID([New Phone Number], 7, 3) <> 555)))

 

AND then combine it with these parameters:

 

=OR([New Phone Number]="",NOT(ISERROR(SEARCH("(???) ???-????",[New Phone Number])=1)))

 

 

Thank you for your help! 

 

No RepliesBe the first to reply

Resources