Finding an Invalid Value Within a Text String

Paul Newcome

Does anyone have an EASY way of finding an invalid value within a text string.

 

For example... I have a field that should contain ONLY letters. No special characters. No numbers.

 

I know I can use a bunch of CONTAINS or FIND functions to search for each number/character, but that gets rather long rather quickly.

 

I know I can parse out the text string character by character using MID statements and hit against a table of valid or invalid characters, but if my text string is 200 characters long, that would exceed the sheet column limit.

 

Ideas?

Alejandra

Hi Paul,

Have you found a solution to this?

I agree, a formula for the example you provided could get lengthy because you'll need to define each of the invalid values. However it depends on what you're searching for. For example, if you have a column that should only contain numbers, you could use ISTEXT to identify text values.

Have you considered changing the column type to a Dropdown so that you could restrict the column to only the preferred values?

Also, if you know of any functions that don't currently exist in Smartsheet that could help you accomplish this, please submit a Product Enhancement Request.