0

I'm trying to create a record repository that will allow a single user the ability to manage uploaded documents based on a record disposal rule system.

I need a column to validate that the uploaded document has a record rule from our system attached to it.

Record rules follow the format: "T ## - ## R ##.##"

How can I make a single line of text column validate that the text entered is in that specific format?
The # should equal any number 0-9.

0

I am not able to tell you the exact formula for your requirement. But use below links for reference which includes some more complex examples of column validations:

  1. SharePoint 2010 Column Validation.
  2. SharePoint Column Validation Examples.

Another Approach:

Use javascript regular expressions to validate your field using PreSaveAction on forms. According to me, this will be quickest way do this.

0

There are many ways to do this... here's one.

If you want to add a Calculated Column with a message:

=IF(
   AND(
     LEN(TheColumn)=17,
     LEFT(TheColumn,2)="T ",
     MID(TheColumn,5,3)=" - ",
     MID(TheColumn,10,3)=" R ",
     ISNUMBER(0+MID(TheColumn,3,2)),
     ISNUMBER(0+MID(TheColumn,8,2)),
     ISNUMBER(0+RIGHT(TheColumn,5))
   ), "OK", "Invalid code")

If you want a validation formula, just remove the IF. The AND will return either True or False.

AND(
  LEN(TheColumn)=17,
  LEFT(TheColumn,2)="T ",
  MID(TheColumn,5,3)=" - ",
  MID(TheColumn,10,3)=" R ",
  ISNUMBER(0+MID(TheColumn,3,2)),
  ISNUMBER(0+MID(TheColumn,8,2)),
  ISNUMBER(0+RIGHT(TheColumn,5))
)

If the decimal point is required to be in the third position only then:

=IF(
   AND(
     LEN(TheColumn)=17,
     LEFT(TheColumn,2)="T ",
     MID(TheColumn,5,3)=" - ",
     MID(TheColumn,10,3)=" R ",
     MID(TheColumn,15,1)=".",
     ISNUMBER(0+MID(TheColumn,3,2)),
     ISNUMBER(0+MID(TheColumn,8,2)),
     ISNUMBER(0+MID(TheColumn,13,2)),
     ISNUMBER(0+RIGHT(TheColumn,2))
   ), "OK", "Invalid code")

or as a validation:

AND(
  LEN(TheColumn)=17,
  LEFT(TheColumn,2)="T ",
  MID(TheColumn,5,3)=" - ",
  MID(TheColumn,10,3)=" R ",
  MID(TheColumn,15,1)=".",
  ISNUMBER(0+MID(TheColumn,3,2)),
  ISNUMBER(0+MID(TheColumn,8,2)),
  ISNUMBER(0+MID(TheColumn,13,2)),
  ISNUMBER(0+RIGHT(TheColumn,2))
)

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.