Google Spreadsheets Regex Case Insensitive (Regexreplace)

regex google-sheets google-apps
nate11000 picture nate11000 · Jul 9, 2014 · Viewed 17.5k times · Source

I'm trying to create a case insensitive regex query in Google Spreadsheets with the regexreplace function. Is that possible? I've tried the \i flag and got a #REF error saying the expression was invalid: =regexreplace("Test","t\i","") gives an error when I would hope to get "es" as the final result.

Is it possible? Is there a flag for case sensitivity in Google Spreadsheets?

Thanks in advance!

Answer

hwnd picture hwnd · Jul 9, 2014

AFAIK, the only way to enable case-insensitive matching is JavaScript API in google docs.

Apparently, RE2 syntax does support the inline (?i) case-insensitive modifier:

=REGEXREPLACE("Test", "(?i)t", "")

An alternative that will work is using a Character class, adding both cases of the letter T..

=REGEXREPLACE("Test", "[Tt]", "")

Related questions

How do I combine COUNTIF with OR

In Google Spreadsheets, I need to use the COUNTIF function on a range with multiple criteria. So in the table below, I would need to have something like =COUNTIF(B:B,"Mammal"or"Bird") and return a value of 4. A |…

Read More
find & replace commas with newline on Google Spreadsheet

I've been trying to figure out how to replace commas with line-breaks. I've tried using (/r/n/), char(10), the Unicode representation (can't remember now), but it rather predictably replaces the comma with whatever I type in the other box, not …

Read More
Extract digits from string - Google spreadsheet

In Google spreadsheets, I need a formula to extract all digits (0 to 9) contained into an arbitrary string, that might contain any possible character and put them into a single cell. Examples (Input -> Output) d32Ελληνικάfe9j.r/3-fF66 …

Read More