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. But--it looks like the format is right? I was looking at this post for guidance:

https://community.smartsheet.com/discussion/71237/flag-when-a-character-is-entered-instead-of-a-number-if-not-isnumber-statement

What am I missing?

Also--does Smartsheet have any plans for data validation? That could be fantastic for forms when people input bad information.

Thanks!

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

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)

kolfinna

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. :)

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.