Flagging bad phone numbers
Hello!
I've been reading around and I feel like I'm missing something with my formula:
=IF(OR(NOT(ISNUMBER([Phone Number]@row))), [Phone Number]@row < 1000000000, [Phone Number]@row > 9999999999, 1, "")
Basically, I want a checkbox to tick if any of the following happens:
 Any characters are added
 It has fewer than 10 digits
 It has more than 10 digits
Feels like I'm missing something super easy with this formula. Butit looks like the format is right? I was looking at this post for guidance:
What am I missing?
Alsodoes Smartsheet have any plans for data validation? That could be fantastic for forms when people input bad information.
Thanks!
Answers

Are your phone numbers going to contain any parenthesis or hyphens or spaces?
If they are a single string of unbroken numbers, you should be able to use:
=IF(OR(ISTEXT([Phone Number]@row), LEN([Phone Number]@row) <> 10), 1)

The phone numbers eventually will, but this is for a helper column that will flag the number if the raw data is incorrectly formatted.
Here's the formula for the formatted phone number:
="(" + LEFT([Phone Number]@row, 3) + ") " + MID([Phone Number]@row, 4, 3) + "" + RIGHT([Phone Number]@row, 4)
So if the phone number column has anything other than a string of 10 digits, it should tick the Phone Not Number cell and highlight the value in the Phone Number column. :)

If that's the case, then the formula above should work to flag it for you.
If there are any special characters in the data in the [Phone Number] column, the format will be converted to text, so we use the ISTEXT function. Then we also use the LEN function to determine how many digits are in the data in the [Phone Number] column and say that if that is not equal to 10 (<> 10). Using both of those as the arguments in the OR function to produce a check if either are true should work for you.
Help Article Resources
Categories
Check out the Formula Handbook template!