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. However, there are lots of string functions we can use to meet our needs! 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*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])=11**and also set our User message we want people to see when we have everything working to, “

**“**

*Must be in the format ###-##-####!*If we test this by trying to add a list item that has only 10 characters, we see our error message.

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.*

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

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

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.

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

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 *

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.

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 *

**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!