MATCH(criterion,array,0) returns #N/A if criterion is text containing '['

[joshua4]

Version: 24.8.6.2 (X86_64) / LibreOffice Community
Build ID: 6d98ba145e9a8a39fc57bcc76981d1fb1316c60c
CPU threads: 20; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

Last year I used a spreadsheet under an earlier version of LO Calc…somehow I got the job done, so it must have worked. This year I find that anywhere I try to MATCH with exact option where the criterion is text containing the left-square-bracket, MATCH categorically returns #N/A.

I’ve enclosed an example. Perhaps I’m missing something obvious. I wanted to see what the AskLO community had to say before downloading the most recent bibisect files and going through that process.

Match and Open Bracket.ods (8.8 KB)

[mikekaganski]

You are indeed missing something obvious: the help article for the function, that mentions regular expressions, where square brackets are metacharacters needing escapement (or you need to disable the regular expressions in options).

[joshua4]

Yep, I should have re-read MATCH. RegexBuddy 5 is in pre-release, and I was playing with the LO feature. Thanks!

Will use something like: =MATCH(SUBSTITUTE(criterion,"[","\["),array,0)

[mikekaganski]

@joshua4

Will use something like

Note the help mentioning a suggestion for a global escaping - otherwise, you better escape everything that could potentially be a metacharacter, like dots, braces, backslashes themselves, dollar signs, …

[joshua4]

…was aware of the limited nature of the searched text. But =MATCH("\Q"&criterion&"\E",array,0) would be better.

[koyotak]

@joshua4

“\Q”&criterion&"\E"

Note the highlighted characters that require “\Q…\E” that I found in REGEX()-functions like MATCH:
Match and Open Bracket_testing characters_020944.ods (36.7 KB)

[mikekaganski]

@koyotak

Note the highlighted characters that require “\Q…\E” that I found

The complete set is specified in the regex documentation of the library used in LibreOffice (that is linked from our help, right at the top). The documentation mentions the “characters that must be quoted” in the specification of \ metacharacter, both for in-set and outside-of-set cases. It’s sad to see the effort spent on “reverse-engineering” of what is freely available
And note that you may avoid the need to check if your data needs to be escaped or not, just by escaping unconditionally.

@joshua4

…was aware of the limited nature of the searched text

Note that this is a public Ask site, where each question can be used by people long after you have resolved your task So we need to strive to make answers as general as possible.

[koyotak]

The help list in that documentation is very confusing and overloaded!
The period character for the end of a sentence works for me without “\Q…\E,” which is strange, and different from the real REGEX function.

[mikekaganski]

@koyotak

The period character for the end of a sentence works for me without “\Q…\E,”

But does it match the literal dot, as opposed to “any single character in that position, including dot itself”?

@koyotak

The help list in that documentation is very confusing and overloaded!

It is technical. The regex topic is also very technical. You can’t handle technical topics, without handling some complexity (or otherwise, you will get caught by gotchas like “but the period character worked for me in my testing” )