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,./()]")

I'm at a loss, I'm not used to working with Regex and would really appreciate some help.

up vote 5 down vote accepted

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.

  • That did it! Thanks a lot for your help! – GuiNetto May 10 '16 at 20:46

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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