Download it now!
Bug 52242 - Strange behaviour when searching numbers
Summary: Strange behaviour when searching numbers
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Blocks: Find-Search
  Show dependency treegraph
Reported: 2012-07-18 17:39 UTC by Johannes Weberhofer
Modified: 2019-08-10 14:36 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

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

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!

* LibreOffice (Build ID: 7122e39-92ed229-498d286-15e43b4-d70da21)
* LibreOffice (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 which I have tested.)
Comment 3 Roman Eisele 2012-07-21 10:23:07 UTC
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, which highlights 9 cells with the matching content. Which version did you use?

The number search bug can still be reproduced with LO
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
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:

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:
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 on osx.
Comment 11 ign_christian 2014-08-27 13:19:26 UTC
Still reproduced in LO - 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
Comment 14 Johannes Weberhofer 2017-05-03 03:55:23 UTC
This bug is still valid for version:
Comment 15 Johannes Weberhofer 2018-05-02 10:24:47 UTC
This bug is still valid for version