CODEWITHSUNDEEP
regexgoogle-sheets
Doug Fir
Doug Fir

Reputation: 21204

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: 1972

Answers (2)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

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: 59442

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

Please try: 

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

Upvotes: 0

Related Questions