I'm trying to find information on how to form proper column validation formulas, but can't find any explanation of formula syntax.

All I keep running into are snippets of formulas, what I'm looking for is a place that teaches me how to create my own.

  • So did you actually find a satisfactory answer? – Frank Fajardo Jul 9 '14 at 5:35

Sharepoint validation uses formulas for calculated fields. Here's a page that shows how you can use functions in formulas: SP2010 calculated field formulas.

  • That list of formulas has a few problems in this context: First, it's not clear how to connect those formula examples of manipulating column contents into a formula for validating column contents. Second, not all possibilities seem to be present. I'm not able to figure out how to ensure that a column only contains a W followed by five or six numerals using the linked page. – Todd Wilcox May 15 '15 at 18:13
  • @ToddWilcox, the Column Validation in a column definition accepts a formula, which must evaluate to true for the value to be considered valid. In your case, you could use: AND(LEN([My Column])=6,LEFT([My Column],1)="W",TEXT(VALUE(RIGHT([My Column],5)),"00000")=RIGHT([My Column],5)). This checks for a "W" followed by 5 digits. Try also reading this and this – Frank Fajardo May 18 '15 at 0:46
  • 1
    The critical piece of information I eventually discovered is the it's an Excel formula. Lots of places say "just use a formula" which doesn't mean much without the word "Excel". With that knowledge, I just put a few fest rows into a worksheet and built an excel formula that evaluated the rows correctly and then copied and pasted that. Thanks. – Todd Wilcox May 18 '15 at 10:33

The secret is the formulas are the exact same ones used in Excel. Make a spreadsheet with a column of test data, some valid and some not, and then built up logical formulas in a nearby column. You'll know you have the formula right when the formula column says "TRUE" for all the valid test data and anything else for invalid data. It doesn't have to say "FALSE" for invalid data, some of my invalids said "#VALUE" and my formula worked. Basically anything not TRUE counts as false. When you copy and paste the formula into sharepoint, omit the IF() function that you have to put around the whole thing in excel. You can use the excel help and trial and error to work out formula syntax.

  • i stumbled upon this thread after some (unfruitful) search on the net and when i realized were excel formulas i understood that the keywords are localized... beware you international users! – Paolo Jul 21 '15 at 10:44

Here is a page from Microsoft's support site containing many examples of common formulas. It contains the following types of examples:

  1. Conditional formulas
  2. Date and time formulas
  3. Mathematical formulas
  4. Text formulas
  • do you know were i can see a list of all the formula functions? the MS site explains some things but leaves a lot to be desired. – user20330 Oct 23 '13 at 12:47
  • The above page has links to the available formula. 1) Conditional formulas 2) Date and time formulas 3) Mathematical formulas 4)Text formulas. What else you want? – Biju Joseph - MCSD Oct 23 '13 at 12:51
  • developer.mozilla.org/en/docs/Web/JavaScript/Guide/… do you see how this site explains regexp? it breaks down the syntax and give examples, the MS site seems to only be examples and a slight explanation of said example. I'm looking for a site similar to the mozilla site – user20330 Oct 23 '13 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.