Bug 52242 - Match numeric values regardless of omissible leading or trailing zeros in Find and Replace dialog
Summary: Match numeric values regardless of omissible leading or trailing zeros in Fin...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.3 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Find-Search
  Show dependency treegraph
 
Reported: 2012-07-18 17:39 UTC by Johannes Weberhofer
Modified: 2022-01-11 14:03 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample file for bug 52242 (7.74 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-07-21 10:14 UTC, Roman Eisele
Details
also strings cannot be found (15.50 KB, application/vnd.ms-excel)
2013-04-23 13:24 UTC, L. Boelen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Johannes Weberhofer 2012-07-18 17:39:59 UTC
It's not easy to search for a calculated number in a table (all tested with locale de_AT):

Pressing Ctrl-F and enter one of the values returned by a formula, the result can not be found.

When the search-dialog is used and one of the formula results in eg. "120,00", I can switch to search the values. Then I can find the cell entering "120". I cant find the value "120,00" or "120.00". But another cell which stores "1120" is also displayed as result.

This behaviour is funny - but not what I expect. I think, when a user enters a number, only values should be searched. When a field stores a numerical value, the comparison should not be a string-comparison but a numerical comparison.
Comment 1 Roman Eisele 2012-07-21 10:14:20 UTC
Created attachment 64485 [details]
Sample file for bug 52242



Thank you very much for your bug report!

REPRODUCIBLE with
* LibreOffice 3.5.5.3 (Build ID: 7122e39-92ed229-498d286-15e43b4-d70da21)
* LibreOffice 3.6.0.2 (Build ID: 815c576)
both with German langpack installed, on MacOS X 10.6.8 (Intel).

In the following, I refer myself to the attached .ods sample file which was created according to reporter's original description. It contains both a cell with a simple formula that should evaluate to 120 (A3 with formula "=SUM(A1:A2)") and two cells with "false positive" values: 1120 (in C2) and 1200 (in C3).

We have to distinguish different cases.


(1) Searching for "120" with the "Find" toolbar, Calc finds the two false-positive cells C2 and C3 only, but never cell A3 with the calculated value 120.

=> IMHO this is not a bug, but a very bad default behaviour which should be changed. There are no options for the "Find" toolbar, so it should act by default the way most "ordinary users" would expect it to work, and I agree with the original reporter that most or all "ordinary users" would expect the "Find" toolbar to find the calculated value "120" in A3, because this (the calculated value "120") is what is visible by default in that cell. So the deault search should be done by comparing values, not by comparing strings, whenever the user searches for a numeric value.


(2) Searching for "120" with the "Find and Replace" dialog window, the results depend from the value of the "Search in" popup menu, which is however hidden by default, because it is in the "More Options" bottom part of the dialog window.

(2a) With the default value for "Search in", which is "Formulas", searching works like with the "Find" toolbar: Calc finds the two false-positive cells C2 and C3 only, but never cell A3 with the calculated value 120. [You can change this behaviour by checking "Entire cells": then no occurence is found at all (correct!).]

=> IMHO this is not a bug, but it is a rather bad default setting that the "Find and Replace" dialog defaults to "Search in": "Formulas". Calc experts may know better, but I would suggest to change the default setting to "Search in": "Values."


(2b) When setting the "Search in" popup menu to "Values", Calc finds both the false-positive cells C2 and C2 and the calculated value in cell A3. OK. But it does *not* find them if I search for 120.00 or 120,00.

=> IMHO there are two real bugs here:

(i) when the user explictly selects "Search in": "Values", searching for 120.00 and/or 120,00 (depending on locale settings) MUST find cell A3 with the calculated value 120, too.

(ii) when the user explictly selects "Search in": "Values", searching for 120, 120.0 or 120,00 should NOT find the false-positive cells with values 1120 (in C2) and 1200 (in C3).
Comment 2 Roman Eisele 2012-07-21 10:17:01 UTC
(Changing version field -- NB: the version field should always contain the FIRST version which is known to contain the bug, not the last one. This issue is probably much older, but for now use 3.5.3.3 which I have tested.)
Comment 3 Roman Eisele 2012-07-21 10:23:07 UTC
@GerardF:
I have reviewed and confirmed this report, but I have to confess that I am no real Calc expert (just an ordinary user what regards Calc). According to our "QA Team" page, you are our spreadsheet expert. Could you please take a look at this report and state your opinion?

I have tried to distinguish different cases in this issue; IMHO there are two cases which are unlucky default settings and two real bugs in this issue (see my comment #1). What do you think about it, especially about the two bugs?

Thank you very much in advance!
Comment 4 L. Boelen 2013-04-23 13:24:35 UTC
Created attachment 78369 [details]
also strings cannot be found

attached file is a valid Microsoft office Excel file.
In LibreOffice I cannot find more than 2 times the string: ketel.
Comment 5 Johannes Weberhofer 2013-04-23 13:50:27 UTC
I have tested the uploaded doc with LO 4.0.2.1, which highlights 9 cells with the matching content. Which version did you use?

The number search bug can still be reproduced with LO 4.0.2.1.
Comment 6 L. Boelen 2013-05-17 09:09:08 UTC
Comment on attachment 78369 [details]
also strings cannot be found

Problem occurs outside any browser
Comment 7 Johannes Weberhofer 2013-05-17 09:18:26 UTC
(In reply to comment #6)
> Comment on attachment 78369 [details]
> also strings cannot be found
> 
> Problem occurs outside any browser

I think, this is not the same bug as reported initially; I can not reproduce your issue with LO 4.0.2.1.
Comment 8 L. Boelen 2013-05-24 07:53:22 UTC
I cannot check LibreOffice 4.0.3 under Ubuntu 12.04 because of incomplete deletion of V3:

.
.
libreoffice4.0-math_4.0.3.3-3_i386.deb
libreoffice4.0-ure_4.0.3.3-3_i386.deb
libreoffice4.0-writer_4.0.3.3-3_i386.deb
lodewijk@DL-mob3eu:~/Downloads/LibreOffice_4.0.3.3_Linux_x86_deb/DEBS$ cd desktop-integration
lodewijk@DL-mob3eu:~/Downloads/LibreOffice_4.0.3.3_Linux_x86_deb/DEBS/desktop-integration$ sudo dpkg -i *.deb
[sudo] password for lodewijk: 
Sélection du paquet libreoffice-debian-menus précédemment désélectionné.
dpkg: betreffende libreoffice4.0-debian-menus_4.0.3-3_all.deb die libreoffice-debian-menus bevat:
 libreoffice-debian-menus is in strijd met libreoffice-bundled
  « libreoffice-core » fournit « libreoffice-bundled » et est présent ainsi de  « geïnstalleerd ».
dpkg: fout bij afhandelen van libreoffice4.0-debian-menus_4.0.3-3_all.deb (--install):
 conflicterende pakketten - libreoffice-debian-menus wordt niet geïnstalleerd
Fouten gevonden tijdens behandelen van:
 libreoffice4.0-debian-menus_4.0.3-3_all.deb
lodewijk@DL-mob3eu:~/Downloads/LibreOffice_4.0.3.3_Linux_x86_deb/DEBS/desktop-integration$
Comment 9 QA Administrators 2013-12-22 21:59:43 UTC Comment hidden (obsolete)
Comment 10 Johannes Weberhofer 2013-12-22 22:47:32 UTC
All information has been provided; don't know why the status needinfo has been set; the issues are still valid for version  4.1.3.2 on osx.
Comment 11 ign_christian 2014-08-27 13:19:26 UTC
Still reproduced in LO 4.2.6.2 - Ubuntu 12.04 x86 -> Platform All
Comment 12 ign_christian 2014-08-27 13:28:34 UTC
Seems to be a dupe to Bug 48456, but I just added in See Also.

But this should be an enhancement, as Bug 48456, so searching could have formatting option.
Comment 13 Johannes Weberhofer 2016-04-16 07:56:17 UTC
This bug is still valid for version 5.1.2.2.0
Comment 14 Johannes Weberhofer 2017-05-03 03:55:23 UTC
This bug is still valid for version: 5.3.0.3
Comment 15 Johannes Weberhofer 2018-05-02 10:24:47 UTC
This bug is still valid for version 6.0.4.1
Comment 16 Johannes Weberhofer 2021-05-03 09:53:27 UTC
Still valid in version 7.1.1.2
Comment 17 Stéphane Guillou (stragu) 2022-01-01 13:22:55 UTC
- Reverting the earliest version affected to correct one as per comment 2
- Issue regarding searching for a result of a formula using the search bar has been fixed in 7.3, which was tracked with bug 102506 (the default for the search bar is now to search for values, not formulas)

Regarding what remains to be looked at in this bug report:

(In reply to Roman Eisele from comment #1)
> [...]
> => IMHO there are two real bugs here:
> 
> (i) when the user explictly selects "Search in": "Values", searching for
> 120.00 and/or 120,00 (depending on locale settings) MUST find cell A3 with
> the calculated value 120, too.
> 
> (ii) when the user explictly selects "Search in": "Values", searching for
> 120, 120.0 or 120,00 should NOT find the false-positive cells with values
> 1120 (in C2) and 1200 (in C3).

I can understand that (i) is a problem: one might want to find "120" when searching for "120.00", because there is a numerical equality. I believe this would require an extra option in the dialog, like "search for numerical values". (note that doing the opposite is already possible: using the "Formatted display" option, users can find a number formatted with e.g. two decimal places "120.00" by searching for the number "120").

Regarding (ii), I disagree that it needs a fix: users often want to search for parts of a string (or number), and if the user only wants the perfect match "120", an not "1120" or "1200", there is the option "Entire cells" for that.

To clarify what this bug is about, I am changing the summary from the original, unspecific "Strange behaviour when searching numbers" to something more specific: "Match numeric values regardless of omissible leading or trailing zeros in Find and Replace dialog"

To clarify further, what I think this bug report is about is:
There should be an option to make a search for "120.00" or "120,00" (depending on locale) match the value "120". Similarly, the same option would allow searching for "000120" and getting the match "120" (or 120.0, etc.). In essence, a "numerical search" of sorts.
Comment 18 Johannes Weberhofer 2022-01-11 13:05:53 UTC
@stragu thanks for having a look at the ticket. Your suggestion for having a "search numerical values" sounds good.