CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formula
Jhonny Alvarez
Jhonny Alvarez

Reputation: 1

Case Sensitive in Google Sheets with REGEXMATCH

I am quite lost, I have read too much about REGEXMATCH on Google Sheets. I am not an expert and I need help.

I have a spreadsheet in Google where I have implemented the REGEXMATCH formula to compare if a text appears between 2 cells.

I try to use the (?i) flag but can't get it to ignore the case.

Example:

=REGEXMATCH(F3;"(?i)I3")

Cell F3 has the text "Digital transformation | Jhonny"

Cell I3 has the text "digital transformation"

My formula returns FALSE because F3 has "Digital" and I3 has "digital".

How can I do please?

Thanks, it's my first post here.

Upvotes: 0

Views: 1715

Answers (2)

Ryszard Czech
Ryszard Czech

Reputation: 18611

I3 is a cell address, it must be concatenated with the strings, for example, with an ampersand.

Simply use

=REGEXMATCH(F3,"(?i).*" & REGEXREPLACE(I3, "([-/\\^$*+?.()|[\]{}])", "\\$1") & ".*")

With REGEXREPLACE(F4, "([-/\\^$*+?.()|[\]{}])", "\\$1"), all special characters will be prepared for use in a regular expression.

The (?i) makes the expression case insensitive.

Upvotes: 0

JvdV
JvdV

Reputation: 75850

Note that the 2nd parameter of REGEXMATCH() expects a string. Right now you do mention "I3" in a literal form, hence the engine will try to literally match this. Therefor you need to concatenate the actuall cell's value into the equation:

=REGEXMATCH(F3;"(?i).*\b"&I3&"\b.*")
  • (?i) - Match remainder case-insensitive.
  • .*\b - Match 0+ characters upto a word-boundary.
  • "&I3&" - This is the part where we insert the value from cell I3.
  • \b.* - Another word-boundary and 0+ characters.

If you do not care about these word-boundaries for a better and more exact match, you do not need REGEXMATCH() at all (since it will also be slower than other functions). Therefor try:

=ISNUMBER(SEARCH(I3,F3))

Upvotes: 1

Related Questions