CODEWITHSUNDEEP
regexgoogle-sheets
Doug Fir
Doug Fir

Reputation: 21302

Regular Expressions in Google Sheets

I'm trying to use regular expressions within Google Sheets. Given that the environment is within GSheets some functionality seems to be missing or, potentially just different.

I would like to use a regexmatch function that returns true if the range in question contains any of the following strings:

"string1" "string2" "string3"

I tried =regexmatch(range,"([Ss]tring1|[[Ss]tring2|[Ss]tring3)"

This works.

But my developer colleague said he would usually just end the expression /i to say "Be case insensitive"

=regexmatch(range,"/(String1|String2|String3)/i"

But since Gsheets does not use "/" to open a regular expression, is there another way to tell the function to ignore case?

Also, is there a way to negate the expression? That is, instead of:

=NOT(regexmatch(range,"([Ss]tring1|[[Ss]tring2|[Ss]tring3)")

Can you do something like

=regexmatch(range,"!=([Ss]tring1|[[Ss]tring2|[Ss]tring3)"

Upvotes: 1

Views: 1975

Answers (2)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5529

you can try wrapping your range with the "lower" function, so compares the values as if they are all lower case regardless of whether they really are or not.

=REGEXMATCH(lower(range),"string1|string2|string3")

Upvotes: 3

pnuts
pnuts

Reputation: 59495

is there another way to tell the function to ignore case?

Please try: 

=regexmatch(range,"(?i)string1|string2|string3")

Upvotes: 0

Related Questions