How to do case insensitive match with regexmatch in google sheets?

user_10712882 0
Viewed 15146

I'm trying to filter my sheet with 

=filter({B:B}, regexmatch({B:B}, $A$1))

Is it possible to make regexmatch case insensitive?

google-sheets regex
5 Answers

Not sure exactly what is $A$1, but the second parameter is still a regular expression, so I am assuming you can pass a flag like:

=filter({B:B}, regexmatch({B:B}, "(?i)in"))

if $A$1 is a string, maybe you can use the concatenate function so the output result is the flag plus your value:

=filter({B:B}, regexmatch({B:B}, CONCATENATE("(?i)", $A$1)))

user_3592260

You can use:

=regexmatch($I1,"(?i)tex")
user_24416370

The braces are not required:

=filter(B:B,regexmatch(B:B,$A$1))

should work for case insensitive just as for case sensitive, provided that for the former the A1 cell is prepended: (?i). That is, if the term is tex, in A1:

(?i)tex
user_15051200

This might work:

 =filter(B:B, lower(regexmatch(B:B)), lower($A$1))

The LOWER function internally supresses all capitals, rendering both the searched data and the search term case equivalent, and without modifying the actual data onscreen.

user_161564060

=filter({B:B}, regexmatch(upper(B:B), upper($A$1)))

user_124246810
Related