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)
I tested with the Golang flavor on regex101 site and confirmed the negative look-ahead is causing the error:
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
(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
.
Any suggestion on solving this new issue?
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:
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.