SharePoint column validation for only numbers with fixed length

Here is how to set column validation formula for SharePoint field to contain only numbers and only two symbols (fixed length). You have to use a “single line of text” field because the number field won’t work this way.

Here is the formula:

=IF(LEN(CustomSortOrder)=2,ISNUMBER(CustomSortOrder+0),FALSE)

This works for both SharePoint 2010 and 2013 and can be set in the user interface.

If you are creating a WSP:

Here is the xml for the field called “CustomSortOrder” is a required field with the deafult value set to “00”:

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <Field
        ID="{[id]}"
        Name="CustomSortOrder"
        DisplayName="Sort Order"
        Type="Text"
        Required="TRUE"
        EnforceUniqueValues="FALSE"
        Indexed="FALSE"
        MaxLength="2"
        Group="CustomField">
    <Default>00</Default>
    <Validation Message="The value of this field must be between 01 and 99 and with 2 characters.">

      =IF(LEN(CustomSortOrder)=2,ISNUMBER(CustomSortOrder+0),FALSE)

    </Validation>
  </Field>
</Elements>

In my search for this solution I found some other formulars at The Chris Kent blog:

• http://thechriskent.com/2012/08/15/validate-phone-number-columns-in-sharepoint/
• http://thechriskent.com/2012/08/16/validate-email-address-columns-in-sharepoint/

Happy SharePointing.

E-mail | Post RSS Permalink | Comments(0)