Monday, June 13, 2011

Column Validation Formula - Phone Number

Scenario
  • You need to force a formatting convention for phone numbers in a list using the list's column validation formulas.
Issue
  • Regular Expressions are not supported. :(
Resolution
  • You must build your functions using Excel 2010 formulas
  • Example for (555) 555-5555 format on a column named "CELL PHONE"
    • =AND(IF(ISERROR(FIND("(", [CELL PHONE],1)), FALSE, (FIND("(", [CELL PHONE]) = 1)), IF(ISERROR(FIND(")", [CELL PHONE],5)), FALSE, (FIND(")", [CELL PHONE], 5) = 5)), IF(ISERROR(FIND(" ", [CELL PHONE],6)), FALSE, (FIND(" ", [CELL PHONE], 6) = 6)), IF(ISERROR(FIND("-", [CELL PHONE],10)), FALSE, (FIND("-", [CELL PHONE], 10) = 10)),IF(ISERROR(1*CONCATENATE(MID([CELL PHONE], 2, 3), MID([CELL PHONE], 7, 3), MID([CELL PHONE], 11, 4))), FALSE, AND(1*CONCATENATE(MID([CELL PHONE], 2, 3), MID([CELL PHONE], 7, 3), MID([CELL PHONE], 11, 4)) > 1000000000, 1*MID([CELL PHONE], 2, 3) <> 911, 1*MID([CELL PHONE], 7, 3) <> 911)))
  • You must use your column name instead of "CELL PHONE"
  • Includes checks for 911

3 comments:

  1. Hi , I've read a few things on this site and I really do think that it has helped tremendously. There's still a heap I need to learn thus can continue learning and can keep coming back. Thanks.
    SharePoint Consulting

    ReplyDelete
  2. This was really helpful, thanks. I wrote up an explanation of the formula you're using and added a couple of minor tweaks on my blog here: http://thechriskent.com/2012/08/15/validate-phone-number-columns-in-sharepoint/

    I've linked back to you, but if there's anything else you'd like me to do, please let me know, thanks again!

    ReplyDelete
  3. Looks good, is there a way of using a phone number extension with the formula that is optional?

    ReplyDelete