OpenOffice / LibreOfiice / Calc - When i am searching for partial text in a column, if text contains the character "?", no results are given! [closed]

OpenOffice / LibreOfiice / Calc - When i am searching for partial text in a column, if text contains the character "?", no results are given!

For Example: =IFERROR(INDEX($A$1:$A279;MATCH(IF(SEARCH($G$1;A80)=1;A80;"");$A$1:$A$200;0));"") If$G\$1 is "aaa" the search formula for text string "aaaaaaaarchg" will result 1 (positive)

HOWever if the text string is "aaaaaaaarchg?" will result 0 (negative).

(? can be anywhere inside the text string).

edit retag reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2020-08-02 14:23:41.543102

Given formula is in row 80, and text string is in A80.

• With Enable wildcards in formulas, the result is aaaaaaaarchg. EDIT: without ?.
• With Enable regular expressions in formulas, the result is aaaaaaaarchg.
• With Enable wildcards in or regular expressions in formulas, the result is aaaaaaaarchg?.

I get no 1 nor 0 result.

EDIT: I am restarting LibreOffice, because I am getting different results now.

( 2020-12-25 18:26:49 +0200 )edit

Sort by » oldest newest most voted

Your question does not seem clear enough to me.
The only objection I can make without knowing more detail (or writing a long essay) is the question whether you are aware of the fact that SEARCH and MATCH both are capable of working with regular expressions, and that the question mark is a "special character" concerning RegEx. If not, you may also not have checked the related setting in 'Calculate' > 'General Calculations'.

If working with Regex is enabled, you must "escape" special caharcters occurring in a string to search for. The search string ab?c will find in UacX and in UabcX ("false" positive), but will not find in Uab?cX ("false" negative) if the RegEx engine is active and ? was meant to be a literal. The search string ab\?c will avoid both these errors. (To search for a literal backslash it must also be escaped).

Very much clearer your quwestion will look as soon as you attach an example Calc document demonstrating your issue. I will see for it that you can use the attachment tool of this forum now.

more