Wow! I never expected to write so many posts about Column Validation. Yet, here I am with the 5th post in what has become a series about Column Validation. Just to recap, here are the links to my previous posts on Column Validation:
- How to Validate a Due Date in a SharePoint 2010 List
- How to Validate Strict Text Formats in SharePoint 2010
- How to Validate an Email Address Using Column Validation in SharePoint 2010
- How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation
Also, if you would like to learn more about these techniques, with live demonstration and discussion, I’ll be speaking on Column Validation at July. 2012 meeting of the Federal SharePoint User’s Group (FEDSPUG) in Washington, DC. Come on out, I would love to meet you!
The Problem
A reader asked, “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?” Yes, this is possible, and in this post I’ll show you how to do it.
In a nutshell, this is an ‘OR’ type of validation that I discussed in my post on How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation, with one of the options being no answer at all. In this post, I’ll look at a slightly different way of doing this than the exact way I did it in that post. The are multiple formulas that may work for many of these complex validation requirements, so there isn’t one single correct answer.
A Simple Non-Required Validation Test
Because some of the formulas for Column Validation can be quite complex, I like to set up a “proof of concept” type validation that is more simple first, then I can work on the more complex details.
So, I’ll first test the concept of validating something only if it exists. Then, once I have that working, I’ll integrate the formula I’ve already written for validating a phone number.
I’ve created a list with a column named “phone”. I’ve chose the “Single line of text” data type, set it to a maximum of 12-characters (three digits, a dash, 3 digits, a dash, and four digits), and made it not required.
Since I only want my column validation to run if the user enters something in the field, that is to say, if the field’s value isn’t NULL, my first thought was to use the ISNULL() function and then wrap it in the NOT() function. (Note: Microsoft has a section with information about available Formulas and Functions in SharePoint 2010.) This method should work fine on Number columns, however, a column of data type Single line of text isn’t NULL if it’s empty, it just has a string of no length. This moved me back in the direction of using nested IF() functions like I did in my post on How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation.
First, we’ll write a simple formula to only allow a blank phone number. This formula will look like this: =IF(LEN([Phone])=0,TRUE,FALSE) With this formula in place, if there is anything in the Phone field, it won’t validate. However, it validates fine if the field is empty. 
Next, we’ll put a simple validation place of the FALSE result. Let’s try to only allow the letter “A” there. So a user can leave it blank, or they can enter a single letter “A”, but nothing else will be allowed. That formula will look like this: =IF(LEN([Phone])=0,TRUE,(IF([Phone]=”A”,TRUE,FALSE))
With this formula, only empty values, or values of a single letter “A” are allowed: 
Validating a Phone Number, But Only If One Is Entered
Now that we have the general logic worked out, we need to replace that second condition ([Phone]=”A”) in the formula with the Phone number validation formula. You’ll remember from the post on How to Validate Strict Text Formats in SharePoint 2010 to validate for ###-###-####, the formula is:
=(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
To make it easier to read, let’s separate the original formula so the parts are on separate lines. It looks like this:
=IF(
LEN([Phone])=0
,TRUE
,(IF([Phone]=”A”
,TRUE
,FALSE
)
)
If we replace the second condition with the Phone Number Validation Condition, it looks like this:
=IF(
LEN([Phone])=0
,TRUE
,(IF(
(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
,TRUE
,FALSE
)
)
Although this does correctly validate and only allow phone numbers in the ###-###-#### format, it doesn’t display our nice friendly error message when a user enters in invalid information. It displays: 
instead of: 
As I discussed in the post on How to Use ‘OR’ Type Options in SharePoint 2010 Column Validation, this is because the formula is trying to evaluate characters that don’t exist. Let’s change our formula to only check the individual characters if it is 12-characters long. With this extra condition, our stub formula will look like this:
=IF(
LEN([Phone])=0
,TRUE
,(IF(LEN([Phone])=12
,TRUE
,FALSE
)
)
This stub formula will work and only allow blank phone numbers or phone numbers that are 12-digits long. To add the testing for the proper characters, we’ll need to wrap the phone number validation formula in an IF() statement and replace the second TRUE result with it. Because this second statement will only run if it’s 12-characters long, we don’t need to include that part of the formula and the sum will be 22 instead of 23. Our formula will look like this:
=IF(
LEN([Phone])=0
,TRUE
,(IF(LEN([Phone])=12
,IF((
(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)
=22
),TRUE,FALSE)
,FALSE
)
)
Whew! That was a lot of work, but we did it. With that formula, our list doesn’t require a phone number to be entered, but if one is entered, it must be in the proper format. 
I’m really enjoying working through these column validation questions and issues and look forward to hearing what others need to validate. Of course, hopefully, the series has been written so that you’ve learned to do this yourself and you’ll be sharing your formulas in the comments instead of asking for new ones. 
Happy validating!