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 
[…] The Chris Kent Quick Fixes, Workarounds, & Neat Tricks I felt like sharing HomeAbout RSS ← Validate Phone Number Columns in SharePoint […]
[…] my previous posts (Phone Validation, Email Address Validation) I showed how to setup column validation for both phone numbers and email […]
[…] I posted a while back about how to use SharePoint column validation to validate email addresses and phone numbers. Those are still good techniques, but by using an InfoPath list item form it’s just a […]
The Email Validation rocks but it doesnt generate my custom error, it presents the user with the default error instead. Any suggestions?
=OR(ISBLANK([Email Address]),AND(ISERROR(FIND(” “,[Email Address],1)),IF(ISERROR(FIND(“@”,[Email Address],2)),FALSE,AND(ISERROR(FIND(“@”,[Email Address],FIND(“@”,[Email Address],2)+1)),IF(ISERROR(FIND(“.”,[Email Address],FIND(“@”,[Email Address],2)+2)),FALSE,FIND(“.”,[Email Address],FIND(“@”,[Email Address],2)+2)<LEN([Email Address])))))
Matt, glad you find the validation useful! By default, if you only specify the formula for a column in the column validation section you’ll get something along the lines of “Column Validation Failed.” directly under the input box for your column (using the standard edit form) AFTER pressing Save.
Obviously this is better than nothing but isn’t super helpful to the end user about what they should do. The second setting in the Column Validation section is the User message. This is what will be shown if what they enter fails your validation formula. Fill out this setting to get a custom error message.
Chris – this is great, thanks for sharing this. One question for you, can you explain line 4 and 5 in your validation expression. Is the FALSE part in line 4 the text that is outputted if the argument is TRUE…I thought it was/is (don’t IS functions end with ,TRUE, FALSE). For line 5, I get that a check is happening again, but don’t understand why there is no TRUE, FALSE part of the argument and why there is =1 is there (I undrestand that the =1 is referrring to the first position which is where you want the parathesis…I just don’t get technically why line 5 has to be there).
Bill, thanks for your feedback! The ISERROR checks complicate things a little but are necessary. The problem is actually with the FIND function. Instead of returning -1 (not found) if the search string, [Phone] in this case, is empty or not long enough – it returns an error which we can’t use logically or compare against.
The ISERROR wrapper takes care of this for us. So the IF statement performs the FIND inside an ISERROR (line 3) just to make sure no nasty errors are going to mess everything up. If ISERROR returns TRUE, the IF statement returns FALSE (line 4) so that the overall AND (line 1) evaluates to FALSE – saying that the phone number is invalid. This also short circuits the unwrapped FIND (line 5) keeping our validation error free. If no error is returned then we’re safe to perform the FIND and know that we can actually look at the value (since there will be one) and see if the position returned is equal to 1 (line 5).
So basically, the first FIND (line 3) doesn’t tell us anything about the position of the parenthesis, we’re just making sure it won’t blow up. It’s the FIND in line 5 that is actually important. This structure of a FIND inside an ISERROR followed by the actual FIND is one you’re going to want to use over and over. Hope that all makes sense!
Ok…I get it now. Whew…being new to functions…this takes a few minutes to wrap my head around. I’m assuming in line 5 that this is the FALSE argument output…which is another FIND function, but this time you’re using the FIND fuction to declare a character (a paranthesis) is in a certain position, rather than using the FIND to actually find it there. Is this correct or no?
I just learned that when validations in SharePoint equal TRUE, they also equal 1. You can combine formuals and have the net result = the number of formulas you are validating. So…that being said, the following formula works as an alternate to the phone number validation you have above.
=(LEN([Phone Number])=14)+
(MID([Phone Number],1,1)=”(“)+
(MID([Phone Number],5,1)=”)”)+
(MID([Phone Number],6,1)=”-“)+
(MID([Phone Number],10,1)=”-“)
=5
Bill, in answer to your first question: The second time we use FIND (line 5) we are checking to see if the parenthesis is really at position 1.
As for your alternate validation,
unfortunately MID is not a supported function for calculated columns (http://msdn.microsoft.com/en-us/library/bb862071(v=office.14).aspx first Note) so I’m afraid this won’t work.Also, although I’m sure you can just use a final = number of validations check, I think the AND as shown above is much clearer.Thanks for your comments, if things are still unclear just let me know!
Thanks Chris, that makes sense. Regarding the use of MID, it’s working great in my environment. As you’ve stated above, maybe it’s just the “calculated” column where this does not work.
Bill, I’m an idiot. Of course MID is an allowed function in Column Validation (I’m doing it myself in line 19 above!). I’ve modified my comment above. Sorry for even more confusion!
You’re right that your validation formula works. However, it’s validating (###)-###-#### rather than (###) ###-####
That might be what you want, but if you want it to be a space instead of a hyphen it wouldn’t be hard to change yours. One thing your’s isn’t doing is ensuring that the numbers are actually numbers. With your formula these can be any character at all. The number validation of my formula could easily be added to yours (lines 19-27). Thanks for all the feedback!
This formula can be made much more efficient by replacing the IF(ISERROR(FIND())) structures with IFERROR(FIND()), like this:
=AND(
LEN([Phone])=14,
IFERROR(FIND(“(“, [Phone],1) = 1, FALSE),
IFERROR(FIND(“)”, [Phone],5) = 5, FALSE),
IFERROR(FIND(” “, [Phone],6) = 6, FALSE),
IFERROR(FIND(“-“, [Phone],10) = 10, FALSE),
IFERROR(AND(
1CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4)) > 1000000000,
1MID([Phone], 2, 3) <> 911,
1MID([Phone], 7, 3) <> 911,
1MID([Phone], 7, 3) <> 555
), FALSE)
)
This cuts the number of calculations significantly, as we no longer have to perform the FINDs and the CONCATENATE twice, throwing away the first result each time.
Actually, we can simplify it even further than that. If anything within the AND produces an error, the AND will produce an error as well, so we don’t even have to convert our individual errors to FALSE. If the AND contains any errors, IFERROR will return FALSE. Otherwise, it will return the result of the AND, which is only TRUE if all of the tests passed.
=IFERROR(
AND(
LEN([Phone])=14,
FIND(“(“, [Phone],1) = 1,
FIND(“)”, [Phone],5) = 5,
FIND(” “, [Phone],6) = 6,
FIND(“-“, [Phone],10) = 10,
1CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4)) > 1000000000,
1MID([Phone], 2, 3) <> 911,
1MID([Phone], 7, 3) <> 911,
1MID([Phone], 7, 3) <> 555
), FALSE
)
Chris-
I’m trying to get the phone number format to be XXX-XXX-XXXX. I changed the validation to this, but I get an error and I can’t figure out what I did wrong. Can you help?
=AND(LEN([Business Phone])=12,
IF(ISERROR(FIND(“-“,[Business Phone],4)),FALSE,(FIND(“(“,[Business Phone])=4)),
IF(ISERROR(FIND(“-“,[Business Phone],8)),FALSE,(FIND(“-“,[Business Phone],8)=8)),
IF(ISERROR(1*CONCATENATE(MID([Business Phone],1,3),MID([Business Phone],5,3),MID([Business Phone],9,4))),FALSE,
AND(1*CONCATENATE(MID([Business Phone],1,3),MID([Business Phone],5,3),MID([Business Phone],9,4))>1000000000,
1*MID([Business Phone], 1, 3) 911,
1*MID([Business Phone], 5, 3) 911,
1*MID([Business Phone], 5, 3) 555
)))
Debbie, there’s an error in line 2 of your formula. In the second FIND you are still looking for a ( instead of a hyphen. Also the < and the > symbols aren’t showing up in the last 3 lines before the final parenthesis but I’m guessing that’s a problem with the comments not with your formula. Thanks for commenting!
Thank you for the post Chris. I am at a stand still on something in the column validation department and I saw this site and figured it wouldn’t hurt to ask your opinion on the matter.
I have columns named [Incremental Spend] and [Incremental Spend Reason]. If [Incremental Spend] is greater than $1, I would like the user to fill out as to why in the [Incremental Spend Reason] column.
I’m pretty sure that I read that I can not use IF statements in the column validation settings. But this is what I had in mind.
=IF([Incremental Spend]>=$1,[Incremental Spend Reason]=1)
I understand you cant throw the $ in there, but for purpose of identifying the issue I did. What do you think? Can something along these lines be accomplished?
Chris –
Thanks for posting this – the explanations are very helpful too. I’m very new to SharePoint development so this is great!
If I copy and paste the formula into my column validation field (I’m also using [Phone] as my field name) and enter (902) on the form and click save, I receive the “error”, “The validation formula has evaluated to an error.”
Validation works on these combinations:
(
(902) 1
(902) 12
(902) 123
(902) 123-
(902) 123-4
(902) 123-45
(902) 123-456
(902) 123-4567
“The validation formula has evaluated to an error.” on these:
(9
(90
(902
(902)
(902)1
(902)123-4567
Any idea where I might be going wrong?
Tim
Hi Chris,
Thanks for posting it. I made a small change in its last part as it is throwing an error “The validation formula has evaluated to an error.” if length of the phone number is less than 14. I added this condition to make sure the remainder part is executed only if we have sufficient length.
IF(LEN([Phone])=14,
IF(ISERROR(1CONCATENATE(MID([VendorPhone], 2, 3), MID([VendorPhone], 7, 3), MID([VendorPhone], 11, 4))),
FALSE,
AND(
1CONCATENATE(MID([VendorPhone], 2, 3), MID([VendorPhone], 7, 3), MID([VendorPhone], 11, 4)) > 1000000000,
1MID([VendorPhone], 2, 3) <> 911,
1MID([VendorPhone], 7, 3) <> 911,
1*MID([VendorPhone], 7, 3) <> 555
)
), FALSE)
[…] https://thechriskent.com/2012/08/15/validate-phone-number-columns-in-sharepoint/ […]