Correct syntax for REGEXEXTRACT, where condition comes from certain cell and case insensitive parame-CodePudding

I have a REGEXMATCH expression, where the regex comes from a certain cell. Like this: =REGEXMATCH(A2, $B$2). In B2, the regex looks like .*abc.*|.*def.*.

I tried to adjust regex with (?i).*abc.*|.*def.*, but it seems like only abc becomes case insensitive.

Should I add (?i) to each part of pipelined regex? - I have many of them, it would be not funny job. Or is it possible anyhow to add (?i) once to all regex parts?

CodePudding user response：

use:

=REGEXMATCH(A2; "(?i).*abc.*|.*def.*")

CodePudding user response：

If you need your REGEXEXTRACT to match regardless of case, and you are looking for the shortest path (least edits), you could consider converting the string you are testing to lowercase inside your formula. ie:

=REGEXEXTRACT(LOWER(A2), $B$2)

This way, your regex is always operating on an all lowercase string, making it case insensitive as a result.