I didn’t originally set out to write a series of posts on Column Validation in SharePoint 2010, but I keep thinking of new challenges each time I do. I have no idea how long this series may go (in fact, this may be the last one), but this is the third in the series. Here are links to the previous two posts:
- How to Validate Strict Text Formats in SharePoint 2010
- How to Validate an Email Address Using Column Validation in SharePoint 2010
In today’s post we’ll look at how we might allow users to enter data in a one of a couple different formats. The example that comes to mind is US Postal Service ZIP Code validation. Two ZIP Code formats are allowed: the old 5-digit format and the ZIP+4 format that we started using the 1980’s (5-digits, followed by a dash, followed by four more digits).
Using what you learned in my first post of the series you can probably easily write a formula to validate each one of those. Instead of walking through each step of writing those, I’ll just write them for you here:
ZIP Code Validation Formula:
=(LEN([ZIP Code])=5)
+(CODE(MID([ZIP Code],1,1))>47)
+(CODE(MID([ZIP Code],1,1))<58)
+(CODE(MID([ZIP Code],2,1))>47)
+(CODE(MID([ZIP Code],2,1))<58)
+(CODE(MID([ZIP Code],3,1))>47)
+(CODE(MID([ZIP Code],3,1))<58)
+(CODE(MID([ZIP Code],4,1))>47)
+(CODE(MID([ZIP Code],4,1))<58)
+(CODE(MID([ZIP Code],5,1))>47)
+(CODE(MID([ZIP Code],5,1))<58)
=11
ZIP+4 Validation Formula:
=(LEN([ZIP Code])=10)
+(CODE(MID([ZIP Code],1,1))>47)
+(CODE(MID([ZIP Code],1,1))<58)
+(CODE(MID([ZIP Code],2,1))>47)
+(CODE(MID([ZIP Code],2,1))<58)
+(CODE(MID([ZIP Code],3,1))>47)
+(CODE(MID([ZIP Code],3,1))<58)
+(CODE(MID([ZIP Code],4,1))>47)
+(CODE(MID([ZIP Code],4,1))<58)
+(CODE(MID([ZIP Code],5,1))>47)
+(CODE(MID([ZIP Code],5,1))<58)
+(MID([ZIP Code],6,1)="-")
+(CODE(MID([ZIP Code],7,1))>47)
+(CODE(MID([ZIP Code],7,1))<58)
+(CODE(MID([ZIP Code],8,1))>47)
+(CODE(MID([ZIP Code],8,1))<58)
+(CODE(MID([ZIP Code],9,1))>47)
+(CODE(MID([ZIP Code],9,1))<58)
+(CODE(MID([ZIP Code],10,1))>47)
+(CODE(MID([ZIP Code],10,1))<58)
=20
Set Up a Sandbox List for Experimenting
Since I’m using these blog posts to teach how to do column validation formatting instead of just giving you the formulas without much explanation, let’s create a sandbox list we can play in and see what is going on. We’ll need 3 columns in the list: a Single line of text for the ZIP Code, a calculated field that uses the first formula for the ZIP Formula, and another calculated field that uses the ZIP+4 Formula. I’ve set that up here and entered a couple examples: 
Because those formulas are so long, we’ll write our initial formula using the ZIP Formula column and ZIP=4 Formula column. Then we’ll replace those with the full formulas.
The Logic – Introducing the Undocumented OR() Formula
Let’s create a new calculated column in our sandbox list and call it Combined Formula. Although I found information about the AND() formula, I didn’t see any documentation about an OR() function. I decided to try it and see if it would work anyway. It did! At least, part way. Enter in a formula of =OR([ZIP Formula],[ZIP+4 Formula]). You’ll notice you get the following results: 
Dealing with Errors
See those #VALUE! Errors? Ultimately, we know those are No’s, but SharePoint sees them as errors. When a validation formula encounters an error it will give the user an error instead of the message you want. Here is a screenshot showing what the user sees when a validation formula encounters an unchecked error. 
What we wanted them to see was this: 
We can handle this with the ISERROR() formula we used in yesterday’s post. We’ll also combine it with the IF() function I mentioned briefly in the first post. The IF() function accepts three comma separated parameters: the item to test, the result if the test is true, and the result if the test is false.
To see this in action, let’s work with one column at a time in our Combined Column. Change the formula for this column to =IF(ISERROR([ZIP Formula]),FALSE,IF([ZIP Formula],TRUE,FALSE)). What this does is test for an error first. If it encounters an error then it sets the field to FALSE, if it doesn’t encounter an error, then it checks to see if the formula evaluates to True or False and sets the value accordingly.
This gives us the accurate Yes’s and No’s about the ZIP Formula, without the errors: 
Now that we have that working with the ZIP Formula column, let’s modify it to use the ZIP+4 Formula column. That’s as simple as changing the column names: =IF(ISERROR([ZIP+4 Formula]),FALSE,IF([ZIP+4 Formula],TRUE,FALSE)).
Notice this also correctly tells us that the last test is the only one that fits our ZIP+4 criteria: 
Back to the Logic
Now that we have the errors all worked out, let’s use the OR() function to see if either of them are valid. Here is what the formula will look like: =OR((IF(ISERROR([ZIP Formula]),FALSE,IF([ZIP Formula],TRUE,FALSE))),(IF(ISERROR([ZIP+4 Formula]),FALSE,IF([ZIP+4 Formula],TRUE,FALSE)))). Notice that this shows us that either of the last two entries are valid, but neither of the first two are: 
Putting It All Together
Chances are pretty good that you don’t want to add two extra columns to your SharePoint list every time you have a ZIP Code column for validation purposes. So we’ll need to put our original formulas in the place of the column names we used in our last formula. If you’re thinking, “This is going to be one really long formula!” then you’re absolutely correct!
To make things easier, let’s take that last formula and break it out on different lines and add some indentation so it’s easier to read:
=OR(
(
IF(ISERROR(
[ZIP Formula]
)
,FALSE
,IF(
[ZIP Formula]
,TRUE
,FALSE
)
))
,(IF(ISERROR(
[ZIP+4 Formula]
)
,FALSE
,IF(
[ZIP+4 Formula]
,TRUE
,FALSE
))
)
)
Now that you have that, simple replace both instances of [ZIP Formula] with the first formula from the beginning of this post and both instances of the [ZIP=4 Formula] with the second formula (remember to drop the equals sign at the beginning and wrap it in parenthesis). This will give you a completed formula that looks like this:
=OR(
(
IF(ISERROR(
((LEN([ZIP Code])=5)
+(CODE(MID([ZIP Code],1,1))>47)
+(CODE(MID([ZIP Code],1,1))<58)
+(CODE(MID([ZIP Code],2,1))>47)
+(CODE(MID([ZIP Code],2,1))<58)
+(CODE(MID([ZIP Code],3,1))>47)
+(CODE(MID([ZIP Code],3,1))<58)
+(CODE(MID([ZIP Code],4,1))>47)
+(CODE(MID([ZIP Code],4,1))<58)
+(CODE(MID([ZIP Code],5,1))>47)
+(CODE(MID([ZIP Code],5,1))<58)
=11)
)
,FALSE
,IF(
((LEN([ZIP Code])=5)
+(CODE(MID([ZIP Code],1,1))>47)
+(CODE(MID([ZIP Code],1,1))<58)
+(CODE(MID([ZIP Code],2,1))>47)
+(CODE(MID([ZIP Code],2,1))<58)
+(CODE(MID([ZIP Code],3,1))>47)
+(CODE(MID([ZIP Code],3,1))<58)
+(CODE(MID([ZIP Code],4,1))>47)
+(CODE(MID([ZIP Code],4,1))<58)
+(CODE(MID([ZIP Code],5,1))>47)
+(CODE(MID([ZIP Code],5,1))<58)
=11)
,TRUE
,FALSE
)
))
,(IF(ISERROR(
((LEN([ZIP Code])=10)
+(CODE(MID([ZIP Code],1,1))>47)
+(CODE(MID([ZIP Code],1,1))<58)
+(CODE(MID([ZIP Code],2,1))>47)
+(CODE(MID([ZIP Code],2,1))<58)
+(CODE(MID([ZIP Code],3,1))>47)
+(CODE(MID([ZIP Code],3,1))<58)
+(CODE(MID([ZIP Code],4,1))>47)
+(CODE(MID([ZIP Code],4,1))<58)
+(CODE(MID([ZIP Code],5,1))>47)
+(CODE(MID([ZIP Code],5,1))<58)
+(MID([ZIP Code],6,1)="-")
+(CODE(MID([ZIP Code],7,1))>47)
+(CODE(MID([ZIP Code],7,1))<58)
+(CODE(MID([ZIP Code],8,1))>47)
+(CODE(MID([ZIP Code],8,1))<58)
+(CODE(MID([ZIP Code],9,1))>47)
+(CODE(MID([ZIP Code],9,1))<58)
+(CODE(MID([ZIP Code],10,1))>47)
+(CODE(MID([ZIP Code],10,1))<58)
=20)
)
,FALSE
,IF(
((LEN([ZIP Code])=10)
+(CODE(MID([ZIP Code],1,1))>47)
+(CODE(MID([ZIP Code],1,1))<58)
+(CODE(MID([ZIP Code],2,1))>47)
+(CODE(MID([ZIP Code],2,1))<58)
+(CODE(MID([ZIP Code],3,1))>47)
+(CODE(MID([ZIP Code],3,1))<58)
+(CODE(MID([ZIP Code],4,1))>47)
+(CODE(MID([ZIP Code],4,1))<58)
+(CODE(MID([ZIP Code],5,1))>47)
+(CODE(MID([ZIP Code],5,1))<58)
+(MID([ZIP Code],6,1)="-")
+(CODE(MID([ZIP Code],7,1))>47)
+(CODE(MID([ZIP Code],7,1))<58)
+(CODE(MID([ZIP Code],8,1))>47)
+(CODE(MID([ZIP Code],8,1))<58)
+(CODE(MID([ZIP Code],9,1))>47)
+(CODE(MID([ZIP Code],9,1))<58)
+(CODE(MID([ZIP Code],10,1))>47)
+(CODE(MID([ZIP Code],10,1))<58)
=20)
,TRUE
,FALSE
))
)
)
Whew! Now that’s what I call validation!
Before we add this to our validation field, let’s test the combined formula in our Combined Formula calculated column. As you can see, this gives us the same result we got before: 
1024 Character Limit for Column Validation Formulas
If you paste this formula into the Column Validation field on the ZIP Code column, you’ll notice that it trims it about half-way. Microsoft is enforcing a 1,024-character limit on Column Validation formulas and this formula has 2,815-characters with the indented spacing. If we remove all the extra spaces, that brings it down to 2,135-characters, bit that’s still 1,111 too many. If we change our column name from ZIP Code to simply ZIP, we can save 7-characters each time we reference it (we don’t have to use the square brackets for single word column names). This brings us down to 1,704-characters. We still need to eliminate 680 characters. Can we do it?
The first formula is now 279-characters and the second formula is now 511-characters. If we can reference each formula only once, that will save 790-characters and get us under our limit. Hmmm…
Rephrasing the Solution
As it turns out, there is a way to only reference each formula one time. We only need to use the ZIP formula if the user entered 5-characters and we only need to use the ZIP+4 formula if the user entered 10-characters. Anything except 5- or 10-characters is not valid, so we don’t need to test that. This also means that we don’t need to do any error checking. 
So, let’s begin with an IF() statement this time that just tests to see if the input is 5-characters long: =IF(LEN(ZIP)=5,TRUE,FALSE).
Then we’ll add in the test for 10-characters long if it fails the 5-character test: =IF(LEN(ZIP)=5,TRUE,(IF(LEN(ZIP)=10,TRUE,FALSE))).
Let’s write this with some indentation so we can see what we’re working with. We can always replace the spaces if we need more characters.
=IF(
LEN(ZIP)=5
,TRUE
,(IF(
LEN(ZIP)=10
,TRUE
,FALSE
))
)
Now, all we need to do is replace the TRUE portions of this formula with the tests we want to run. We can also exclude the length test now too. So, here is what the formula looks like:
=IF(
LEN(ZIP)=5
,( (CODE(MID(ZIP,1,1))>47)
+(CODE(MID(ZIP,1,1))<58)
+(CODE(MID(ZIP,2,1))>47)
+(CODE(MID(ZIP,2,1))<58)
+(CODE(MID(ZIP,3,1))>47)
+(CODE(MID(ZIP,3,1))<58)
+(CODE(MID(ZIP,4,1))>47)
+(CODE(MID(ZIP,4,1))<58)
+(CODE(MID(ZIP,5,1))>47)
+(CODE(MID(ZIP,5,1))<58)
=10)
,(IF(
LEN(ZIP)=10
,((CODE(MID(ZIP,1,1))>47)
+(CODE(MID(ZIP,1,1))<58)
+(CODE(MID(ZIP,2,1))>47)
+(CODE(MID(ZIP,2,1))<58)
+(CODE(MID(ZIP,3,1))>47)
+(CODE(MID(ZIP,3,1))<58)
+(CODE(MID(ZIP,4,1))>47)
+(CODE(MID(ZIP,4,1))<58)
+(CODE(MID(ZIP,5,1))>47)
+(CODE(MID(ZIP,5,1))<58)
+(MID(ZIP,6,1)="-")
+(CODE(MID(ZIP,7,1))>47)
+(CODE(MID(ZIP,7,1))<58)
+(CODE(MID(ZIP,8,1))>47)
+(CODE(MID(ZIP,8,1))<58)
+(CODE(MID(ZIP,9,1))>47)
+(CODE(MID(ZIP,9,1))<58)
+(CODE(MID(ZIP,10,1))>47)
+(CODE(MID(ZIP,10,1))<58)
=19)
,FALSE
))
)
That’s 1088-characters with the spaces for indentation—816 if we remove all the spaces. We did it! In fact, it could probably made even smaller by checking the first five characters separately from the last 5. I’ll leave that as a homework exercise for you though. 
Implementing the Solution
Now let’s try out the formula, add a friendly validation message, and try to enter some valid and invalid Zip Codes.
Notice that anything that doesn’t meet our strict criteria displays our friendly error message: 
What Have We Learned?
I’ve been all over the place in this post, but I think you’ve learned a lot. Let me summarize:
- It is possible to have multiple options that are valid (‘OR’ type options).
- This can be accomplished through IF() functions and/or the OR() function.
- Validation formulas that result in an error, rather than TRUE or FALSE, will show an unfriendly error message instead of the friendly error the user entered.
- ISERROR() can be used to trap errors if they may be encountered.
- There is a 1024-character limit to Column Validation formulas.
- Columns with a single name don’t need square brackets around them in formulas.
- The most obvious formulas can often be shortened by looking at the problem a different way.
Is there anything I haven’t touched on in these three posts? What other kinds of validation have you not been able to do? Let me know in the comments!