Need formula to fulfill requirement where SS doesn't control input
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!
Best Answer

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
Answers

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

Good answer. Thank you Genevieve!!
Help Article Resources
Categories
Check out the Formula Handbook template!