In this post, I am trying to shed some light on making a case-insensitive regular expression match using the Regexmatch in Google Sheets.

In addition to that, you can learn how to make only part of a regular expression case-sensitive.

As far as I know, Regexmatch (RE2 expressions) and Query are the functions that use regular expressions, especially for text matching. This post is about the former one.

Notes:-

1. In Google Sheets QUERY, we can use the MATCHES string comparison operator for a (preg) regular expression match.
2. There are two more functions in Google Sheets that use RE2 regular expressions. They are REGEXREPLACE and REGEXEXTRACT. Other than matching, they perform additional tasks.

What Is Case-Sensitivity in a Piece of Text Matching?

It’s all about whether the piece of text is sensitive or insensitive to the capitalization of letters.

In other words, whether uppercase and lowercase letters are treated as distinct or equivalent.

Please see the below table.

I want to match the values in column 1 with the values in column 2 in two different ways.

I have done the same, and the outputs in columns 3 and 4 are self-explanatory.

Case-Insensitive Regexmatch in Google Sheets (Whole)

Let’s start with the basic formulas first (whole matching).

We will later discuss how to make only part of a regular expression case-sensitive in Google Sheets.

With the Help of Text Functions Lower or Upper

We usually use the text function LOWER or UPPER for case-insensitive regexmatch in Google Sheets.

Assume we have input the text abCDef in cell A1 and inserted the following REGEXMATCH formula in cell C1.

=regexmatch(A1,"abcdef")

Syntax: REGEXMATCH(text, regular_expression)

It will return FALSE because the formula is sensitive to the capitalization of the letters.

We can use the LOWER function as below for case-insensitive Regexmatch in Google Sheets.

=regexmatch(lower(A1),"abcdef")

Use capital letters in the regular expression when using the UPPER function.

=regexmatch(upper(A1),"ABCDEF")

If the regular_expression is cell reference, for example, B1, you should use lower(B1) or upper(B1) depending on A1 capitalization.

=regexmatch(upper(A1),upper(B1))

With the Help of a Pattern Modifier

There is one drawback in using the Lower/Upper text functions for case-insensitive Regexmatch in Google Sheets. What’s that?

When using either of these two functions, we can’t make part of the Regexmatch regular expression case-sensitive.

In that scenario, the pattern modifier, i.e., (?i), does the job. We will come to that later.

First, learn about replacing the above two functions (Upper/Lower) with the said pattern modifier.

This time, in C1, we can use the below formula.

=regexmatch(A1, "(?i)abcdef")

We can follow the following method when using cell references in both arguments.

=regexmatch(A1,"(?i)"&B1)

Making Only Part of a Regular Expression Case-Sensitive in Regexmatch

Please see the texts in cells A4 and A5. There you can see the texts highlighted in blue and red color. What do they mean?

Blue Color:- Uppercase and lowercase letters should be treated as distinct.

Red Color:- Uppercase and lowercase letters should be treated as equal.

I have coded the formulas accordingly in cells C4 and C5. Here are them.

Note:- The coloring has nothing to do with the formula. It’s solely for the explanation purpose.

C4:

=regexmatch(A4,"Apple(?i)orange(?-i)")

C5:

=regexmatch(A5,"(?i)(apple.*)(?-i)ORANGE")

Here is a real-life example of only making part of a regular expression case-sensitive.

In column A, I have a few texts that are country names and codes combined.

I have used the hyphen separator to separate the country codes from names.

Sample Data in A2:A:

How to check whether all the country codes are entered in capital letters?

First, we will extract the country codes using one Array Formula in cell B2.

=ArrayFormula(upper(IFNA(TRIM(regexextract(A2:A,"\-(.*)")))))

The above formula extracts all the country codes and makes them the upper case.

Then in cell C1, we will use one Regexmatch formula.

=ArrayFormula(if(A2:A="",,regexmatch(A2:A,"(?i)(.*)(?-i)"&B2:B)))

In this formula, we have left part of the regular expression case-sensitive.

That’s all about case-insensitive Regexmatch in Google Sheets. Thanks for the stay. Enjoy!

Resources

• How to Use Exact Function in Google Sheets [Case Sensitive and Insensitive].
• Case Sensitive Vlookup in Google Sheets [Solved].
• Regexmatch Dates in Google Sheets – Single/Multiple Match.
• Multiple OR in Conditional Formatting Using Regex in Google Sheets.
• Regex to Replace the Last Occurrence of a Character in Google Sheets.
• Split a Text after Every Nth Word in Google Sheets (Using Regex and Split).
• Regex to Get All Words after Nth Word in a Sentence in Google Sheets.
• How to Replace Commas within or outside Brackets in Google Sheets – Regex.