Column Validation for SharePoint list that combines two formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2636419%22%20slang%3D%22en-US%22%3EColumn%20Validation%20for%20SharePoint%20list%20that%20combines%20two%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2636419%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20SharePoint%20list%20where%20a%20phone%20number%20entry%20needs%20data%20validation.%20I%20am%20new%20at%20validation%20formulas%20and%20cannot%20figure%20out%20how%20to%20combine%20the%20two.%20I%20want%20to%20confirm%20that%20the%20number%20is%20a%20valid%20phone%20number%2C%20not%20a%20toll-free%20number%20or%20a%20fake%20number.%20I%20also%20want%20the%20user%20to%20be%20able%20to%20submit%20the%20form%20with%20nothing%20at%20all%20in%20this%20field%20(in%20the%20instance%20they%20do%20not%20have%20a%20phone%20number%20to%20update.%20I%20have%20these%20two%20formulas%20I%20want%20to%20be%20combined%3A%3C%2FP%3E%3CP%3E%3DAND(%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BLEN(%5BNew%20Phone%20Number%5D)%3D14%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIF(ISERROR(FIND(%22(%22%2C%20%5BNew%20Phone%20Number%5D%2C1))%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BFALSE%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B(FIND(%22(%22%2C%20%5BNew%20Phone%20Number%5D)%20%3D%201)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B)%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIF(ISERROR(FIND(%22)%22%2C%20%5BNew%20Phone%20Number%5D%2C5))%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BFALSE%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B(FIND(%22)%22%2C%20%5BNew%20Phone%20Number%5D%2C%205)%20%3D%205)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B)%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIF(ISERROR(FIND(%22%20%22%2C%20%5BNew%20Phone%20Number%5D%2C6))%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BFALSE%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B(FIND(%22%20%22%2C%20%5BNew%20Phone%20Number%5D%2C%206)%20%3D%206)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B)%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIF(ISERROR(FIND(%22-%22%2C%20%5BNew%20Phone%20Number%5D%2C10))%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BFALSE%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B(FIND(%22-%22%2C%20%5BNew%20Phone%20Number%5D%2C%2010)%20%3D%2010)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B)%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIF(ISERROR(1*CONCATENATE(MID(%5BNew%20Phone%20Number%5D%2C%202%2C%203)%2C%20MID(%5BNew%20Phone%20Number%5D%2C%207%2C%203)%2C%20MID(%5BNew%20Phone%20Number%5D%2C%2011%2C%204)))%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BFALSE%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BAND(%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B1*CONCATENATE(MID(%5BNew%20Phone%20Number%5D%2C%202%2C%203)%2C%20MID(%5BNew%20Phone%20Number%5D%2C%207%2C%203)%2C%20MID(%5BNew%20Phone%20Number%5D%2C%2011%2C%204))%20%26gt%3B%201000000000%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B1*MID(%5BNew%20Phone%20Number%5D%2C%202%2C%203)%20%26lt%3B%26gt%3B%20888%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B1*MID(%5BNew%20Phone%20Number%5D%2C%207%2C%203)%20%26lt%3B%26gt%3B%20800%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B1*MID(%5BNew%20Phone%20Number%5D%2C%207%2C%203)%20%26lt%3B%26gt%3B%20555)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAND%20then%20combine%20it%20with%20these%20parameters%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D%3C%2FSPAN%3E%3CSPAN%3EOR%3C%2FSPAN%3E%3CSPAN%3E(%5BNew%20Phone%20Number%5D%3D%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3ENOT%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EISERROR%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ESEARCH%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%22(%3F%3F%3F)%20%3F%3F%3F-%3F%3F%3F%3F%22%3C%2FSPAN%3E%3CSPAN%3E%2C%5BNew%20Phone%20Number%5D)%3D%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3E)))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you%20for%20your%20help!%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2636419%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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! 

 

0 Replies