Using regex in Google Sheets data validation

Question

I'm trying to use a regex in the Google Sheets data validation in order to ban the input of special characters, so basically my allowed set should be [a-zA-Z0-9,./()].

I'm using the custom formula input with the following =REGEXMATCH(TO_TEXT(range),"^[^a-zA-Z0-9,./()]") but it now rejects everything including what is in my allowed set.

I've also tried the following variations and the results were the same as the above (everything is rejected):

=REGEXMATCH(TO_TEXT(range),"^[a-zA-Z0-9,./()]")

=REGEXMATCH(TO_TEXT(range),"[a-zA-Z0-9,./()]")

=REGEXMATCH(TO_TEXT(range),"[^a-zA-Z0-9,./()]")

=REGEXMATCH("[a-zA-Z0-9,./()]")

=REGEXMATCH("[^a-zA-Z0-9,./()]")

=REGEXMATCH("^[a-zA-Z0-9,./()]")

=REGEXMATCH("^[^a-zA-Z0-9,./()]")

Answer 1

The regular expression should be ^[a-zA-Z0-9,./()]*$, where $ at the end means the end of the string. This forces the entire contents to consist of the specified characters.

Also, you don't normally pass a range into data validation formulas: it should be the reference to the upper left corner of the range. For example, if your range is A2:E20, the formula in data validation should be

=REGEXMATCH(TO_TEXT(A2), "^[a-zA-Z0-9,./()]*$")

This formula will then be interpreted for other cells according to the way relative references are treated. This is the same behavior as with conditional formatting.