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!
