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