=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)))
Posts
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/
ReplyDeleteI've linked back to you, but if there's anything else you'd like me to do, please let me know, thanks again!
Looks good, is there a way of using a phone number extension with the formula that is optional?
ReplyDeleteWould you be interested by exchanging hyperlinks? casino games
ReplyDeletewww.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.
ReplyDeletewww.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.