Applies To: SharePoint 2010
The column validation feature of SharePoint 2010 lists can save you a lot of headache and prevent problems before list items are ever added. I recently added a Phone column to a custom list and wanted to validate it was an actual phone number. Unfortunately, my handy dandy regular expression that I always use in .NET wouldn’t work here since you are limited to using the calculated field formulas (Excel).
Some quick searching turned up Robert Freeman’s blog where he gives a great formula for ensuring phone numbers are entered in the form (###) ###-####. This worked perfectly for me. However, I wanted to make sure I understood it so I broke it out into logical sections and went through it.
I’ve taken his formula and added 2 additional checks (Length = 14 and ensuring the number doesn’t start with 555) and formatted it to be more readable:
=AND(
LEN([Phone])=14,
IF(ISERROR(FIND("(", [Phone],1)),
FALSE,
(FIND("(", [Phone]) = 1)
),
IF(ISERROR(FIND(")", [Phone],5)),
FALSE,
(FIND(")", [Phone], 5) = 5)
),
IF(ISERROR(FIND(" ", [Phone],6)),
FALSE,
(FIND(" ", [Phone], 6) = 6)
),
IF(ISERROR(FIND("-", [Phone],10)),
FALSE,
(FIND("-", [Phone], 10) = 10)
),
IF(ISERROR(1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4))),
FALSE,
AND(
1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4)) > 1000000000,
1*MID([Phone], 2, 3) <> 911,
1*MID([Phone], 7, 3) <> 911,
1*MID([Phone], 7, 3) <> 555
)
)
)
To get this working in SharePoint, just copy the above and do a find and replace on [Phone] with whatever your column is named. SharePoint will remove all the extra line breaks and make it ugly again as soon as you save it.
What’s Happening
Column Validation works on the simple principal of returning true or false. So Freeman starts his formula in line 1 with an AND statement. An AND statement allows you to pass multiple conditions (also returning true or false) and will return true only if all the conditions return true. This is a great way to ensure that multiple checks all validate correctly.
The first check in line 2 uses the LEN function to ensure that the total length of the entry is exactly 14 characters (10 numbers, 2 parenthesis, a space, and a dash). This is a slight improvement over the original since any entry with a properly formatted phone number at the start could be used. This prevents someone from adding extra numbers or characters.
The second check in lines 3-6 uses the FIND function to see if the first character is the (. It’s wrapped in an ISERROR check because the FIND function returns an error if the string is not as long as the start number (in this case, empty) or if the text wasn’t found at all. So if it wasn’t found (error), we return false. If it was found then there is a check to see where it was found (FIND returns the character number where the search text was found). In this case we want it in position 1.
The next 3 checks in lines 7-18 are essentially the same as above just looking for the closing parenthesis in position 5, a space after the parenthesis in position 6 and a dash in position 10.
In line 19, a check is performed to ensure that the number portions of the entry are actually numbers. The 3 sections of numbers are put together using a CONCATENATE function and are multiplied by 1. If the multiplication fails (because the string can’t be converted to a number) the error condition is caught by the ISERROR wrapper and FALSE is returned. An ISNUMBER function would have worked here as well. If it’s a number, then some additional checks are performed in a new AND clause beginning in line 21.
Line 22 uses the CONCATENATE function to ensure that not only are the numbers numbers, but also that they aren’t all zeroes.
Lines 23-25 use the MID function to ensure that the area code and the prefix don’t equal 911 and that the prefix also doesn’t equal 555 since these would not be valid numbers.
That’s it! Open your list settings and edit/add your phone column and expand the column validation section and paste the formula from above in there:
Side Note: The above formula will automatically make this a required column since the validation doesn’t allow blank columns. An easy fix for this is to wrap the above formula in an OR statement with an ISBLANK function. So something like this:
=OR(ISBLANK([YourColumn]),AndStatementFromAbove)
More information and a full example can be found on my Requirement Groups entry.
The Chris Kent 