0

UPDATE:

I just learned the Golang flavor is the right one for re2 on the regex101 site. I also checked the Github syntax page for re2 and found that:

(?!re)  before text not matching re (NOT SUPPORTED)

d0

I tested with the Golang flavor on regex101 site and confirmed the negative look-ahead is causing the error:

d1

So next my updated question became:

Would there be an alternative to the negative look-ahead function available for re2?

I found those 2 SO questions on this topic:

PCRE to RE2 regex conversion with negative lookahead

Negative look-ahead in Go regular expressions

And the third comment of the 2nd question seems to provide a working solution hint with regex example here: https://regex101.com/r/aM5oU3/4

Using this regex:

BBB[^B]*?EEE

I adapted it ot my case as this:

(?i)\b([^(or|be)])\b(?i)\b(my|be|or)\b

Now it returns better but still not yet as expected on the minimal example with the updated formula:

=ArrayFormula(IF(REGEXMATCH(trim(regexreplace(regexreplace(D2:D,"(?:([A-Z]([a-z]){1,}))|.", " $1"), "(\s)([A-Z])","$1 $2")),"(?i)\b(my|be|or)\b"), 
trim(regexreplace(regexreplace(regexreplace(D2:D,"(?:([A-Z]([a-z]){1,}))|.", " $1"), "(\s)([A-Z])","$1 $2"),"\b[^(or|my)]\b(?i)\b(my|be|or)\b", " ")),
trim(regexreplace(regexreplace(D2:D,"(?:([A-Z]([a-z]){1,}))|.", " $1"), "(\s)([A-Z])","$1 $2"))))

With correct expected result would be as:

Testing Match
Or Match
And Be

f

(I noticed now the prior expected output was partially wrong as it wouldn't need returninng the lower case "be" or "or" occurences as per the replace function specification (sorry for that, I didn't catch it prior as with all the new concepts and logic acrobatics I got attention dissipated on that).)

But with a Be as input in D2 it still returns it, when it would be expected it not returning the Be.

g

Any suggestion on solving this new issue?

Sample Sheet Update


I have this formula that removes some words with the corresponding regex:

=ArrayFormula(IF(REGEXMATCH(trim(regexreplace(regexreplace(D2:D,"(?:([A-Z]([a-z]){1,}))|.", " $1"), "(\s)([A-Z])","$1 $2")),"(?i)\b(my|be|or)\b"), 
trim(regexreplace(regexreplace(regexreplace(D2:D,"(?:([A-Z]([a-z]){1,}))|.", " $1"), "(\s)([A-Z])","$1 $2"),"\b(?i)(?!or|be)\b(?i)\b(my|be|or)\b", " ")),
trim(regexreplace(regexreplace(D2:D,"(?:([A-Z]([a-z]){1,}))|.", " $1"), "(\s)([A-Z])","$1 $2"))))
=ArrayFormula(
  IF(
    REGEXMATCH(
      trim(
        regexreplace(
          regexreplace(
            D2:D,
            "(?:([A-Z]([a-z]){1,}))|.",
            " $1"
          ),
          "(\s)([A-Z])",
          "$1 $2"
        )
      ),
      "(?i)\b(my|be|or)\b"
    ),
     trim(
      regexreplace(
        regexreplace(
          regexreplace(
            D2:D,
            "(?:([A-Z]([a-z]){1,}))|.",
            " $1"
          ),
          "(\s)([A-Z])",
          "$1 $2"
        ),
        "\b(?i)(?!or|be)\b(?i)\b(my|be|or)\b",
        " "
      )
    ),
     trim(
      regexreplace(
        regexreplace(
          D2:D,
          "(?:([A-Z]([a-z]){1,}))|.",
          " $1"
        ),
        "(\s)([A-Z])",
        "$1 $2"
      )
    )
  )
)

I need it to allow for some exceptions of or|be, on this minimal testing sample:

Testing for my Match.
Or for or Match.
And Be for be.

The expected result should be:

Testing Match
Or or
Be be

I tried this "\b(?i)(?!or|be)\b(?i)\b(my|be|or)\b" but it's not working in Google Sheets, while it is in this tester: https://regex101.com/r/Liw6hg/1.

Screenshots:

xo

x3

I also looked at those other answers but could not adapt them succesfully:

A regular expression to exclude a word/string

Convert regular expression into re2 that works in Google Spreadsheets?

How to exclude a specific string constant?

Any solution tip is greatly appreciated.

Sample Sheet

Lod
  • 657
  • 1
  • 9
  • 30

0 Answers0