Need formula to fulfill requirement where SS doesn't control input

Ken Hankoff · 11/06/21

I need help building a formula that looks at an adjacent cell and returns something (X or 1 or whatever) for the logic shown below:

If content in the cell is not alphanumeric( e.g. special characters - neither a letter nor a number).

And/or content in the cell is fewer than 3 characters or greater than 8 characters.

It would be great if Smartsheet allowed us to limit the number of characters in a field, but since they don't (I've submitted a Feature Request), the next best thing will be to capture situations where thresholds are exceeded in an adjacent cell, so I can leverage Conditional Formatting or trigger an Alert.

Thanks Community!

Genevieve P. · 11/08/21

Hi @Ken Hankoff

The second half of your formula is the easy one. There's a function called LEN which counts how many characters are in a cell:

=LEN([Column 1]@row)

This includes spaces. It means you can use an IF Statement for the range you're looking for:

=IF(OR(LEN([Column 1]@row) < 3, LEN([Column 1]@row) >= 8), 1, 0)

However your first criteria will be a bit tricker. I don't know of a way to search for special characters in general, so you will need to use the FIND function and specify every single possible special character.

For example, CHAR(33) represents an exclamation mark, so if you're searching for !, then try:

=IF(FIND(CHAR(33), [Task Name]@row) > 0, 1, 0)

You would then want to embed all the options in the same OR statement above:

=IF(OR(LEN([Column 1]@row) < 3, LEN([Column 1]@row) >= 8, FIND(CHAR(33), [Task Name]@row) > 0, FIND(CHAR(63), [Task Name]@row) > 0), 1, 0)

There's a list of codes on this Wikipedia page. The number for inside of the CHAR() function is in the Octal column.

Cheers,

Genevieve

Genevieve P. · 11/08/21