Ask Your Question
1

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

asked 2015-12-22 12:41:12 +0200

estatistics gravatar image

updated 2020-08-02 14:23:22 +0200

Alex Kemp gravatar image

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 flag offensive 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

Comments

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.

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

1 Answer

Sort by » oldest newest most voted
0

answered 2015-12-22 21:06:46 +0200

Lupp gravatar image

updated 2015-12-23 00:32:36 +0200

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2015-12-22 12:41:12 +0200

Seen: 206 times

Last updated: Dec 23 '15