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.
Created attachment 64485 [details]
Sample file for bug 52242
Thank you very much for your bug report!
* LibreOffice 184.108.40.206 (Build ID: 7122e39-92ed229-498d286-15e43b4-d70da21)
* LibreOffice 220.127.116.11 (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).
(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 18.104.22.168 which I have tested.)
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!
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.
I have tested the uploaded doc with LO 22.214.171.124, which highlights 9 cells with the matching content. Which version did you use?
The number search bug can still be reproduced with LO 126.96.36.199.
Comment on attachment 78369 [details]
also strings cannot be found
Problem occurs outside any browser
(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 188.8.131.52.
I cannot check LibreOffice 4.0.3 under Ubuntu 12.04 because of incomplete deletion of V3:
lodewijk@DL-mob3eu:~/Downloads/LibreOffice_184.108.40.206_Linux_x86_deb/DEBS$ cd desktop-integration
lodewijk@DL-mob3eu:~/Downloads/LibreOffice_220.127.116.11_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:
Dear Bug Submitter,
This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information.
For more information about our NEEDINFO policy please read the wiki located here:
If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed.
Thank you for helping us make LibreOffice even better for everyone!
All information has been provided; don't know why the status needinfo has been set; the issues are still valid for version 18.104.22.168 on osx.
Still reproduced in LO 22.214.171.124 - Ubuntu 12.04 x86 -> Platform All
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.
This bug is still valid for version 126.96.36.199.0
This bug is still valid for version: 188.8.131.52
This bug is still valid for version 184.108.40.206
Still valid in version 220.127.116.11
- 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.
@stragu thanks for having a look at the ticket. Your suggestion for having a "search numerical values" sounds good.