How to Validate Strict Text Formats in SharePoint 2010

We often need to ensure that users enter text information in a specific way. For example, perhaps our company uses part numbers such as TL-1400 and RW-1555. Every part number begins with two letters, followed by a dash, followed by four digits. Or perhaps we use Social Security Numbers in SharePoint (if you do, I’m sure you have the appropriate security in place) and need to insure they always follow the same format of three digits, a dash, two digits, a dash, and four digits. We may want to make sure that phone numbers always follow the format of three digits, a dash, three digits, a dash, and four digits. We may want to make sure that an email address field contains some characters, an ampersand, some more characters, a dot, and some more characters (as well as doesn’t include spaces and other special characters).

In this post, I’m going to explain how to validate these types of information and put you on the path to validating your own text formatting in your organization. This is a great method to use when creating a Site Column for frequently used information that needs strict formatting.

Several people have written about how to use SharePoint Designer to modify forms to do this type of validation but that is only a solution for a single instance of usage. If you’re running Enterprise Edition, you can modify your list forms in InfoPath and do formatting validation there. However, once again, it only solves the problem for that particular list.

Both the SharePoint Designer and InfoPath solutions use Regular Expressions. This is a very easy way to validate information (although it’s not so easy to learn). Although SharePoint 2010 does allow Column Validation, Regular Expressions are not supported. Sad smile However, there are lots of string functions we can use to meet our needs! Smile Click here to access information about the formulas that are available in SharePoint 2010 and how to use them.

Validate a Social Security Number in SharePoint 2010

Let’s begin by seeing how we might validate a Social Security Number in SharePoint 2010. I’ve created a custom list and added a column of data type Single line of text named SSN. image

Our requirements are that the field must be 11 characters long—consisting of three digits, a dash, two digits, a dash, and four digits. Let’s edit the column and create a formula to check only the number of characters. We’ll use the formula =Len([SSN])=11and also set our User message we want people to see when we have everything working to, “Must be in the format ###-##-####!
image

If we test this by trying to add a list item that has only 10 characters, we see our error message.
image

So, we have the first piece of criteria we need. We’ll remember that and work on another piece. Let’s validate that the fourth character is a dash. To do this, we’ll use the MID function like so: =MID([SSN],4,1=”-“). This tells SharePoint to extract a string out of the MIDdle of our field that is 1 character long and begins with the character in the 4th position, then check to see if it is a dash.
image

If we try to enter any value that doesn’t have a dash as the fourth character, we see the error.
image

However, it accepts anything that has a dash in that position.
image

The next thing we want to do is combine these two formulas. You would think we could use the AND operator like so: =LEN([SSN])=11 AND (MID(SSN,4,1)="-"). However SharePOint doesn’t like that and will tell us “The formula contains a syntax error or is not supported.” Ugh! We could use the IF function, but it creates a mess that looks like this: =IF(LEN([SSN])=11,(IF(MID([SSN],4,1)="-",TRUE,FALSE)),FALSE). Yuch! In fact, double Yuch! If you think that’s difficult to read (not to mention write and debug) with only two pieces of criteria, wait until we have 21 pieces of criteria! That’s just not usable at all. So, is there anything else we can do? Yes there is!

A TRUE value equates to the number 1 while a FALSE value equates to 0. We can use this fact to create a formula that is easier to read, like so:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
=2

This tells SharePoint to take the result of the first condition, add it to the result of the second condition and see if the combined result is 2. If it is, our formula will evaluate to TRUE.image

With this formula, both pieces of criteria must be met for SharePoint to allow the entry:
image

Now that we have a simple way to check for multiple conditions, we can add a check for the second dash in the seventh position:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
+(MID([SSN],7,1)="-")
=3
image

The next thing we want to do is make sure that the first character is a digit. To do this, we’ll look at it’s ASCII value. Click here for a chart you can use to look up ASCII values. The number 0 has an ASCII value of 48 and the number 9 has an ASCII value of 57. So we can do two checks to see if the value is greater than 47 and less than 58. We’ll use the CODE function to get the value. If we add these two tests to our function, it will look like this:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
+(MID([SSN],7,1)="-")
+(CODE(MID([SSN],1,1))>47)
+(CODE(MID([SSN],1,1))<58)
=5

Now, our field must be eleven characters long, have a dash as the fourth character, have a dash as the seventh character, and must begin with a number.
image

Now you know everything you need to know to finish the formula to have it check for digits in the other eight positions. Here is the finished formula:
=(LEN([SSN])=11)
+(MID([SSN],4,1)="-")
+(MID([SSN],7,1)="-")
+(CODE(MID([SSN],1,1))>47)
+(CODE(MID([SSN],1,1))<58)
+(CODE(MID([SSN],2,1))>47)
+(CODE(MID([SSN],2,1))<58)
+(CODE(MID([SSN],3,1))>47)
+(CODE(MID([SSN],3,1))<58)
+(CODE(MID([SSN],5,1))>47)
+(CODE(MID([SSN],5,1))<58)
+(CODE(MID([SSN],5,1))>47)
+(CODE(MID([SSN],5,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
+(CODE(MID([SSN],6,1))>47)
+(CODE(MID([SSN],6,1))<58)
=21
image

Validate a Telephone Number in SharePoint 2010

We can use these these same techniques to validate a phone number. Here is a formula to validate for ###-###-#### format:
=(LEN([Phone])=12)
+(CODE(MID([Phone],1,1))>47)
+(CODE(MID([Phone],1,1))<58)
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(MID([Phone],4,1)="-")
+(CODE(MID([Phone],5,1))>47)
+(CODE(MID([Phone],5,1))<58)
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(MID([Phone],8,1)="-")
+(CODE(MID([Phone],9,1))>47)
+(CODE(MID([Phone],9,1))<58)
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
=23

And here is a formula to validate for (###)###-#### format:
=(LEN([Phone])=13)
+(MID([Phone],1,1)="(")
+(CODE(MID([Phone],2,1))>47)
+(CODE(MID([Phone],2,1))<58)
+(CODE(MID([Phone],3,1))>47)
+(CODE(MID([Phone],3,1))<58)
+(CODE(MID([Phone],4,1))>47)
+(CODE(MID([Phone],4,1))<58)
+(MID([Phone],5,1)=")")
+(CODE(MID([Phone],6,1))>47)
+(CODE(MID([Phone],6,1))<58)
+(CODE(MID([Phone],7,1))>47)
+(CODE(MID([Phone],7,1))<58)
+(CODE(MID([Phone],8,1))>47)
+(CODE(MID([Phone],8,1))<58)
+(MID([Phone],9,1)="-")
+(CODE(MID([Phone],10,1))>47)
+(CODE(MID([Phone],10,1))<58)
+(CODE(MID([Phone],11,1))>47)
+(CODE(MID([Phone],11,1))<58)
+(CODE(MID([Phone],12,1))>47)
+(CODE(MID([Phone],12,1))<58)
+(CODE(MID([Phone],13,1))>47)
+(CODE(MID([Phone],13,1))<58)
=24

Validate a Part Number in SharePoint 2010

In my opening paragraph I mentioned the possibility of a part number in the format of two letters, a dash, and four numbers such as TL-1400 or RW-1555. We could validate this with:
=(LEN([PartNum])=7)
+(CODE(MID([PartNum],2,1))>64)
+(CODE(MID([PartNum],2,1))<90)
+(CODE(MID([PartNum],3,1))>64)
+(CODE(MID([PartNum],3,1))<90)
+(MID([PartNum],4,1)="-")
+(CODE(MID([PartNum],5,1))>47)
+(CODE(MID([PartNum],5,1))<58)
+(CODE(MID([PartNum],6,1))>47)
+(CODE(MID([PartNum],6,1))<58)
+(CODE(MID([PartNum],7,1))>47)
+(CODE(MID([PartNum],7,1))<58)
+(CODE(MID([PartNum],8,1))>47)
+(CODE(MID([PartNum],8,1))<58)
=14

More…

I hope this has been helpful. Feel free to leave helpful formulas you’ve developed in the comments!

12 Comments

  1. SharePoint Help — Blog — How to Validate an Email Address Using Column Validation in SharePoint 2010 - December 13, 2011, 8:11 am

    [...] « How to Validate Strict Text Formats in SharePoint 2010 by: Ricky [...]

  2. SharePoint Help — Blog — How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation - December 14, 2011, 8:00 am

    [...] How to Validate Strict Text Formats in SharePoint 2010 [...]

  3. Avatar of Tom

    Tom - March 19, 2012, 3:45 pm

    I have form that does not require the “phone” to be filled in. However I want to validate the entry if/when it is filled in.

    Is there a way to add string at end of the phone validation formula to accept a “null” value to avoid validation error?

    Thanks in advance for replies.

  4. SharePoint Help — Blog — How to Validate a Non-Required Column in SharePoint 2010 - March 27, 2012, 1:28 pm

    [...]  How to Validate Strict Text Formats in SharePoint 2010 [...]

  5. Avatar of Ricky Spears

    Ricky Spears - March 27, 2012, 1:47 pm

    snowiboy – I decided to write a new blog post in this series to answer your question. You’ll find it here:
    http://sharepointsolutions.com/sharepoint-help/blog/2012/03/how-to-validate-a-non-required-column-in-sharepoint-2010/

  6. Avatar of Prasad

    Prasad - April 24, 2012, 9:00 am

    Great post.

    I need to validate column with below requirements.
    1. Can contain 0 to 9 numbers
    2. Can contain dash -, plus +, left and right parenthesis ( ), dot . and space
    I found out one formula in Excel but it doesn’t work in SharePoint :( (as SUBSTITUTE is not supported in SharePoint)
    =ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A7,”.”,”"),”)”,”"),”(“,”"),” “,”"),”+”,”"),”-”,”")))

    Other one I found out is below but it is too long for character limit of Column Validation as our Column name is long and we cannot avoid it or change it :(
    =NOT(OR(IFERROR(SEARCH(“a”,[Contact Secondary Number]),0),IFERROR(SEARCH(“b”,[Contact Secondary Number]),0),IFERROR(SEARCH(“c”,[Contact Secondary Number]),0),IFERROR(SEARCH(“d”,[Contact Secondary Number]),0),IFERROR(SEARCH(“e”,[Contact Secondary Number]),0),IFERROR(SEARCH(“f”,[Contact Secondary Number]),0),IFERROR(SEARCH(“g”,[Contact Secondary Number]),0),IFERROR(SEARCH(“h”,[Contact Secondary Number]),0),IFERROR(SEARCH(“i”,[Contact Secondary Number]),0),IFERROR(SEARCH(“j”,[Contact Secondary Number]),0),IFERROR(SEARCH(“k”,[Contact Secondary Number]),0),IFERROR(SEARCH(“l”,[Contact Secondary Number]),0),IFERROR(SEARCH(“m”,[Contact Secondary Number]),0),IFERROR(SEARCH(“n”,[Contact Secondary Number]),0),IFERROR(SEARCH(“o”,[Contact Secondary Number]),0),IFERROR(SEARCH(“p”,[Contact Secondary Number]),0),IFERROR(SEARCH(“q”,[Contact Secondary Number]),0),IFERROR(SEARCH(“r”,[Contact Secondary Number]),0),IFERROR(SEARCH(“s”,[Contact Secondary Number]),0),IFERROR(SEARCH(“t”,[Contact Secondary Number]),0),IFERROR(SEARCH(“u”,[Contact Secondary Number]),0),IFERROR(SEARCH(“v”,[Contact Secondary Number]),0),IFERROR(SEARCH(“w”,[Contact Secondary Number]),0),IFERROR(SEARCH(“x”,[Contact Secondary Number]),0),IFERROR(SEARCH(“y”,[Contact Secondary Number]),0),IFERROR(SEARCH(“z”,[Contact Secondary Number]),0)))

    Please guide me on this. Desperately waiting for answer.

  7. Avatar of Ricky Spears

    Ricky Spears - April 24, 2012, 10:34 am

    pkondhare – I may be able to help you, but you haven’t fully explained your requirements. For example, what is the maximum length of the allowed input? When you say it can contain 0-9 numbers, I interpret this to mean that it’s field where the user can be empty or it can be 9-digits long. Is that what you mean? I think I understand what you mean with the allowed symbols, but is that in addition to the 9-digits or in place of the 9-digits (assuming you meant a 9-character long field). Finally, in your last example you are checking for lower-case letters and you didn’t mention anything in the requirements about letters–only numbers and symbols. You’re going to have to be much more specific about what is allowed and disallowed. If you can explain the business context that would be helpful as well.

    I saw your duplicate comment on the other entry and I’ll be deleting that one since it’s a duplicate–one comment on one post is enough unless the second one contains different inforamtion.

  8. Avatar of Arknev

    Arknev - May 8, 2012, 2:28 pm

    I would suggest a modification to the SSN formula, I added an IFERROR function at the beginning and added added CODE for each position in the SSN. This will prevent the “formula returned error” message when the formula returns #VALUE!. This will occur when there are too few characters entered.

    With Dashes:

    =IFERROR( (LEN(SSN)=11)
    +(MID(SSN,4,1)=”-”)
    +(MID(SSN,7,1)=”-”)
    +(CODE(MID(SSN,1,1))>47)
    +(CODE(MID(SSN,1,1))47)
    +(CODE(MID(SSN,2,1))47)
    +(CODE(MID(SSN,3,1))47)
    +(CODE(MID(SSN,5,1))47)
    +(CODE(MID(SSN,6,1))47)
    +(CODE(MID(SSN,8,1))47)
    +(CODE(MID(SSN,9,1))47)
    +(CODE(MID(SSN,10,1))47)
    +(CODE(MID(SSN,11,1))47)
    +(CODE(MID(SSN,1,1))47)
    +(CODE(MID(SSN,2,1))47)
    +(CODE(MID(SSN,3,1))47)
    +(CODE(MID(SSN,4,1))47)
    +(CODE(MID(SSN,5,1))47)
    +(CODE(MID(SSN,6,1))47)
    +(CODE(MID(SSN,7,1))47)
    +(CODE(MID(SSN,8,1))47)
    +(CODE(MID(SSN,9,1))<58)=19,FALSE)

  9. Avatar of Arknev

    Arknev - May 8, 2012, 2:29 pm

    Here is without dashes

    =IFERROR((LEN(SSN)=9)
    +(CODE(MID(SSN,1,1))>47)
    +(CODE(MID(SSN,1,1))47)
    +(CODE(MID(SSN,2,1))47)
    +(CODE(MID(SSN,3,1))47)
    +(CODE(MID(SSN,4,1))47)
    +(CODE(MID(SSN,5,1))47)
    +(CODE(MID(SSN,6,1))47)
    +(CODE(MID(SSN,7,1))47)
    +(CODE(MID(SSN,8,1))47)
    +(CODE(MID(SSN,9,1))<58)=19,FALSE)

  10. Avatar of Arknev

    Arknev - May 8, 2012, 2:33 pm

    Argh, looks like my reply got messed up last try:

    With Dash

    =IFERROR((LEN(SSN)=11)
    +(MID(SSN,4,1)=”-”)
    +(MID(SSN,7,1)=”-”)
    +(CODE(MID(SSN,1,1))>47)
    +(CODE(MID(SSN,1,1))47)
    +(CODE(MID(SSN,2,1))47)
    +(CODE(MID(SSN,3,1))47)
    +(CODE(MID(SSN,5,1))47)
    +(CODE(MID(SSN,6,1))47)
    +(CODE(MID(SSN,8,1))47)
    +(CODE(MID(SSN,9,1))47)
    +(CODE(MID(SSN,10,1))47)
    +(CODE(MID(SSN,11,1))47)
    +(CODE(MID(SSN,1,1))47)
    +(CODE(MID(SSN,2,1))47)
    +(CODE(MID(SSN,3,1))47)
    +(CODE(MID(SSN,4,1))47)
    +(CODE(MID(SSN,5,1))47)
    +(CODE(MID(SSN,6,1))47)
    +(CODE(MID(SSN,7,1))47)
    +(CODE(MID(SSN,8,1))47)
    +(CODE(MID(SSN,9,1))<58)=19,FALSE)

  11. Avatar of Lisa

    Lisa - May 21, 2013, 4:14 pm

    Thank you for writing about validation. I’m trying to validate a list vs. column because I need to look at more than one field. Based on reading your columns I thought the following would work, however I get the message that my formula validates to an error. What am I doing wrong.

    =If([Day]=”Sat”
    +[Hours]=8, TRUE,
    If([Day]“Sun”
    +[Hours]<5, TRUE,
    FALSE), FALSE)

    So what I'm trying to do is validate to True if the day is Sat and hours =8 or if the day is not Sun and hours are less than 5.

  12. Using Regular Expressions in SharePoint Lists - May 30, 2013, 2:24 pm

    [...] can use the Column Validation section at the bottom of the settings page to enforce certain syntax. This article does a good job of illustrating how to use string functions in the Column Validation [...]

Leave a Reply