Google Sheets Regex – Using Regex in Data Validation

google sheetsregex

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

Best Answer

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.

Related Solutions

Google Sheets – RegExp for Data Validation

The quick start tutorial at https://developers.google.com/apps-script/quickstart/macros shows how to write a JavaScript function in the App Script editor and call it from a Google spreadsheet.

In this case the function can use a regexp such as

/\d+@[^,]+(,\d+@[^,]+)*/

That means one or more digits followed by an @ and one or more non-comma characters, followed by zero or more repetitions of a comma and the same pattern.

That pattern will match anywhere in the given string. If you want to match against the entire input string, i.e. not allow other characters before or afterwards, then start the pattern with /^ and end it with $/ instead of plain / and /. If you then want to allow white space before or after the pattern, you can add \s+ right after the ^ and/or before the $.

You'll want to call a JavaScript regexp method like test() to test a string against the pattern. Example using a simpler pattern (just comma separated numbers):

function check(input) {
  return /^\d+(,\d+)*$/.test(input);
}

There are web sites containing tools for interactive testing of regular expressions. That's a good way to go since these things can be tricky and hard to read. Build it up slowly and test it thoroughly. I didn't test the expressions above. You should test that they meet your needs.

Edit: Per https://support.google.com/docs/answer/3098292 you don't need to call JavaScript to use regular expressions in Google spreadsheets. Example formula:

=REGEXMATCH(A1, "^\d+$")

using a simpler pattern that matches a string of digits.

Google Sheets – Query Language with Regex Matching

To handle word boundaries, you need where A matches instead of where A contains. It allows the use of regular expressions such as .*\bdrew\b.*, meaning

containing the string "drew"
with word boundaries \b left and right of the string (i.e., the characters adjacent to it cannot be word characters)
with any other characters elsewhere in the string: .*

The last bullet item is needed because the logic of matches requires the entire content of A to match the given expression (unlike regexmatch function, which will attempt to match substrings).

So, in your case it would be

=query(A:B, "select * where A matches '.*\bdrew\b.*'")

or, if you want to also match "Drew" (case insensitive), then

=query(A:B, "select * where lower(A) matches '.*\bdrew\b.*'")

Best Answer

Related Solutions

Google Sheets – RegExp for Data Validation

Google Sheets – Query Language with Regex Matching

Related Topic