1

I'm looking for a validation formula to validate a site column in SharePoint site and make it only accept only lower case English characters, numbers, and only hyphen, I tried below formula but it's not working, it says formula is incorrect.

=AND(NOT(ISERROR(FIND("a",Field))),NOT(ISERROR(FIND("b",Field))),NOT(ISERROR(FIND("c",Field))),NOT(ISERROR(FIND("d",Field))),(ISERROR(FIND("e",Field))),
NOT(ISERROR(FIND("f",Field))),NOT(ISERROR(FIND("g",Field))),NOT(ISERROR(FIND("h",Field))),NOT(ISERROR(FIND("i",Field))),NOT(ISERROR(FIND("j",Field))),NOT(ISERROR(FIND("k",Field))),NOT(ISERROR(FIND("l",Field))),
NOT(ISERROR(FIND("m",Field))),NOT(ISERROR(FIND("n",Field))),NOT(ISERROR(FIND("o",Field))),NOT(ISERROR(FIND("p",Field))),NOT(ISERROR(FIND("q",Field))),NOT(ISERROR(FIND("r",Field))),NOT(ISERROR(FIND("s",Field))),NOT(ISERROR(FIND("t",Field))),
NOT(ISERROR(FIND("u",Field))),NOT(ISERROR(FIND("v",Field))),NOT(ISERROR(FIND("w",Field))),NOT(ISERROR(FIND("x",Field))),NOT(ISERROR(FIND("y",Field))),NOT(ISERROR(FIND("z",Field))),NOT(ISERROR(FIND("-",Field))))

Small portion of the formula:

=AND(NOT(ISERROR(FIND("a",Field))),NOT(ISERROR(FIND("b",Field))))

Should accept value in text field example: abc-123

Should not accept: أ ب ج

Any help would be much appreciated.

Update I've constructed below formula , it's working fine but still I need to add 2 more letters to validate and it says the formula is wrong when I add the 2 letters

Working formula without remaining 2 letters is:

=AND(ISERROR(FIND("أ";Slug));ISERROR(FIND("ا";Slug));ISERROR(FIND("ء";Slug));ISERROR(FIND("ئ";Slug));ISERROR(FIND("ؤ";Slug));ISERROR(FIND("ب";Slug));ISERROR(FIND("ت";Slug));ISERROR(FIND("ث";Slug));ISERROR(FIND("ج";Slug));ISERROR(FIND("ح";Slug));ISERROR(FIND("خ";Slug));ISERROR(FIND("د";Slug));ISERROR(FIND("ذ";Slug));ISERROR(FIND("ر";Slug));ISERROR(FIND("ز";Slug));ISERROR(FIND("س";Slug));ISERROR(FIND("ش";Slug));ISERROR(FIND("ص";Slug));ISERROR(FIND("ض";Slug));ISERROR(FIND("ط";Slug));ISERROR(FIND("ظ";Slug));ISERROR(FIND("ع";Slug));ISERROR(FIND("غ";Slug));ISERROR(FIND("ق";Slug));ISERROR(FIND("ف";Slug));ISERROR(FIND("ك";Slug));ISERROR(FIND("ل";Slug));ISERROR(FIND("م";Slug));ISERROR(FIND("ن";Slug));ISERROR(FIND("ه";Slug)))

Invalid formula is:

=AND(ISERROR(FIND("أ";Slug));ISERROR(FIND("ا";Slug));ISERROR(FIND("ء";Slug));ISERROR(FIND("ئ";Slug));ISERROR(FIND("ؤ";Slug));ISERROR(FIND("ب";Slug));ISERROR(FIND("ت";Slug));ISERROR(FIND("ث";Slug));ISERROR(FIND("ج";Slug));ISERROR(FIND("ح";Slug));ISERROR(FIND("خ";Slug));ISERROR(FIND("د";Slug));ISERROR(FIND("ذ";Slug));ISERROR(FIND("ر";Slug));ISERROR(FIND("ز";Slug));ISERROR(FIND("س";Slug));ISERROR(FIND("ش";Slug));ISERROR(FIND("ص";Slug));ISERROR(FIND("ض";Slug));ISERROR(FIND("ط";Slug));ISERROR(FIND("ظ";Slug));ISERROR(FIND("ع";Slug));ISERROR(FIND("غ";Slug));ISERROR(FIND("ق";Slug));ISERROR(FIND("ف";Slug));ISERROR(FIND("ك";Slug));ISERROR(FIND("ل";Slug));ISERROR(FIND("م";Slug));ISERROR(FIND("ن";Slug));ISERROR(FIND("ه";Slug));ISERROR(FIND("و";Slug));ISERROR(FIND("ي";Slug)))

1 Answer 1

2
+100

AND function in SharePoint Calculated column and validation formulas only support up to 30 parameters/conditions.

If you have more than 30 conditions, you can concatenate the multiple conditions use &.

Try using this formula for your requirements:

=AND(ISERROR(FIND("أ";Slug));ISERROR(FIND("ا";Slug));ISERROR(FIND("ء";Slug));ISERROR(FIND("ئ";Slug));ISERROR(FIND("ؤ";Slug));ISERROR(FIND("ب";Slug));ISERROR(FIND("ت";Slug));ISERROR(FIND("ث";Slug));ISERROR(FIND("ج";Slug));ISERROR(FIND("ح";Slug));ISERROR(FIND("خ";Slug));ISERROR(FIND("د";Slug));ISERROR(FIND("ذ";Slug));ISERROR(FIND("ر";Slug));ISERROR(FIND("ز";Slug));ISERROR(FIND("س";Slug));ISERROR(FIND("ش";Slug));ISERROR(FIND("ص";Slug));ISERROR(FIND("ض";Slug));ISERROR(FIND("ط";Slug));ISERROR(FIND("ظ";Slug));ISERROR(FIND("ع";Slug));ISERROR(FIND("غ";Slug));ISERROR(FIND("ق";Slug));ISERROR(FIND("ف";Slug));ISERROR(FIND("ك";Slug));ISERROR(FIND("ل";Slug));ISERROR(FIND("م";Slug));ISERROR(FIND("ن";Slug));ISERROR(FIND("ه";Slug)))&AND(ISERROR(FIND("و";Slug));ISERROR(FIND("ي";Slug)))

OR

Try using nested AND conditions like:

=AND(AND(ISERROR(FIND("أ";[Slug]));ISERROR(FIND("ا";[Slug]));ISERROR(FIND("ء";[Slug]));ISERROR(FIND("ئ";[Slug]));ISERROR(FIND("ؤ";[Slug]));ISERROR(FIND("ب";[Slug]));ISERROR(FIND("ت";[Slug]));ISERROR(FIND("ث";[Slug]));ISERROR(FIND("ج";[Slug]));ISERROR(FIND("ح";[Slug]));ISERROR(FIND("خ";[Slug]));ISERROR(FIND("د";[Slug]));ISERROR(FIND("ذ";[Slug]));ISERROR(FIND("ر";[Slug]));ISERROR(FIND("ز";[Slug]));ISERROR(FIND("س";[Slug]));ISERROR(FIND("ش";[Slug]));ISERROR(FIND("ص";[Slug]));ISERROR(FIND("ض";[Slug]));ISERROR(FIND("ط";[Slug]));ISERROR(FIND("ظ";[Slug]));ISERROR(FIND("ع";[Slug]));ISERROR(FIND("غ";[Slug]));ISERROR(FIND("ق";[Slug]));ISERROR(FIND("ف";[Slug]));ISERROR(FIND("ك";[Slug]));ISERROR(FIND("ل";[Slug]));ISERROR(FIND("م";[Slug]));ISERROR(FIND("ن";[Slug]));ISERROR(FIND("ه";[Slug])));ISERROR(FIND("و";[Slug]));ISERROR(FIND("ي";[Slug])))

Note:

  1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  2. Use correct display name of your SharePoint columns in above formula.
  3. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

Official Documentations:

  1. Calculated Field Formulas
2
  • 1
    Thank you Ganesh, the second formula worked fine for me. Aug 9, 2023 at 8:21
  • Great, I am glad it worked for you! Aug 9, 2023 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.