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

4 comments:

  1. 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
  2. Looks good, is there a way of using a phone number extension with the formula that is optional?

    ReplyDelete
  3. Would you be interested by exchanging hyperlinks? casino games

    ReplyDelete
  4. www.mcafee.com/activate registered trademarks, company names, product names and brand names are the property of their respective owners, and mcafee.com/activate disclaims any ownership in such third-party marks. The use of any third party trademarks, logos, or brand names is for informational purposes only, and does not imply an endorsement by mfmcafee.com or vice versa or that such trademark owner has authorized mfmcafee.com to promote its products or services.


    www.office.com/setup is an independent support and service provider for the most secure remote technical services for all Office products. Our independent support services offer an instant support for all software related errors in the devices, laptops, desktops and peripherals. We have no link or affiliation with any of the brand or third-party company as we independently offer support service for all the product errors you face while using the Office. If your product is under warranty, then you may also avail our support services for free from manufacturer’s official website office.com/setup.

    mcafee activate is an independent support and service provider for the most secure remote technical services for all norton products. Our independent support services offer an instant support for all software related errors in the devices, laptops, desktops and peripherals. We have no link or affiliation with any of the brand or third-party company as we independently offer support service for all the product errors you face while using the norton. If your product is under warranty, then you may also avail our support services for free from manufacturer’s official website norton setup.

    ReplyDelete