1

I working on google sheets files where any special case character (any character other than [a-z,A-Z,0-9] ) is forbidden so to confirm this condition is true I need to check that every character in the cell isn't equal to any special case character.

2 Answers 2

0

Welcome! One of the many possible ways (not the most correct, but visual) - replace all the correct, valid characters with an empty string, just remove them from the cell text, and compare the result with an empty string: the result of the comparison will return TRUE if as a result of such a replacement there are no left in the text no character and FALSE otherwise.

=REGEXREPLACE(A1,"[0-9,a-z,A-Z]","")=""

Or try to extract all characters from the cell text that are not valid. If there are no such characters, then the REGEXEXTRACT() function will return an error and this will be a signal that the cell is "correct":

=ISERROR(REGEXEXTRACT(A1,"[^0-9,a-z,A-Z]"))
0

You could use conditional formatting to get a visual representation of cells with special case characters. All you need to do is apply this formula on the whole sheet or on the range of cells you prefer:

=len(REGEXEXTRACT(A1,"[^0-9a-zA-Z]"))

regex

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.