How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Saturday

Case-Insensitive Regexmatch in Google Sheets (Part or Whole)

Posted by   on

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.






























Text 1Text 2Output
(Sensitive to Capitalization)
Output
(In-sensitive to Capitalization )
AppleAPPLEFALSETRUE
OrangeOrangeTRUETRUE
ORANGEAPPLEFALSEFALSE
table#1

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.


Case-Insensitive Regexmatch Formula - Part of Regular Expression

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:


















United States of America – US
India – in
Uruguay – UY
Spain – es


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

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!