Bug Hunting Session
Bug 38125 - Wildcards "*" need to operate the same as in Excel
Summary: Wildcards "*" need to operate the same as in Excel
Status: RESOLVED DUPLICATE of bug 72196
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.3 release
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 61990 78516 (view as bug list)
Depends on:
Blocks:
 
Reported: 2011-06-09 14:29 UTC by Roderick
Modified: 2014-10-20 11:24 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
file copied from Microsoft's documentation, demonstrating bug (4.59 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-08-14 20:13 UTC, David Roundy
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Roderick 2011-06-09 14:29:54 UTC
Wildcards "*"  need to operate the same as Excel, especially in a formula such as =MATCH(text&"*",range,0)
Comment 1 David Roundy 2011-08-16 16:15:50 UTC
I'll just add that this affects DSUM, COUNTIF and more.  It seems a pretty important bug to me, since it causes libreoffice to give wrong answers when opening an Excel spreadsheet.  I'm not sure what the best option is.  Perhaps since there is already an option to either use regular expressions or not, there could be a third alternative, which is to use Excel-compatible wildcard matching.

For documentation, you can see:

http://office.microsoft.com/en-us/excel-help/wildcard-characters-HP005203612.aspx

This page doesn't make it clear where these wildcards are supported, but they are partially documented in the COUNTIF documentation:

http://office.microsoft.com/en-us/excel-help/countif-HP005209029.aspx

and they definitely work in DSUM as well, which is what brought it to my attention due to wrong results in accounting spreadsheets.
Comment 2 Björn Michaelsen 2011-12-23 12:29:08 UTC
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 3 Florian Reisinger 2012-08-14 14:04:23 UTC
Dear bug submitter!

Due to the fact, that there are a lot of NEEDINFO bugs with no answer within the last six months, we close all of these bugs.

To keep this message short, more infos are available @ https://wiki.documentfoundation.org/QA/NeedinfoClosure#Statement

Thanks for understanding and hopefully updating your bug, so that everything is prepared for developers to fix your problem.

Yours!

Florian
Comment 4 Florian Reisinger 2012-08-14 14:05:13 UTC
Dear bug submitter!

Due to the fact, that there are a lot of NEEDINFO bugs with no answer within the last six months, we close all of these bugs.

To keep this message short, more infos are available @ https://wiki.documentfoundation.org/QA/NeedinfoClosure#Statement

Thanks for understanding and hopefully updating your bug, so that everything is prepared for developers to fix your problem.

Yours!

Florian
Comment 5 Florian Reisinger 2012-08-14 14:09:40 UTC
Dear bug submitter!

Due to the fact, that there are a lot of NEEDINFO bugs with no answer within the last six months, we close all of these bugs.

To keep this message short, more infos are available @ https://wiki.documentfoundation.org/QA/NeedinfoClosure#Statement

Thanks for understanding and hopefully updating your bug, so that everything is prepared for developers to fix your problem.

Yours!

Florian
Comment 6 Florian Reisinger 2012-08-14 14:11:39 UTC
Dear bug submitter!

Due to the fact, that there are a lot of NEEDINFO bugs with no answer within the last six months, we close all of these bugs.

To keep this message short, more infos are available @ https://wiki.documentfoundation.org/QA/NeedinfoClosure#Statement

Thanks for understanding and hopefully updating your bug, so that everything is prepared for developers to fix your problem.

Yours!

Florian
Comment 7 David Roundy 2012-08-14 20:13:03 UTC
Created attachment 65565 [details]
file copied from Microsoft's documentation, demonstrating bug

This file is just a simple demonstration that libreoffice still has buggy handling of wildcards.  It's an excel file that should have a value of "4", but instead has a value of "0" in the place marked with the arrow "<- ".
Comment 8 David Roundy 2012-08-14 20:15:51 UTC
I've checked with libreoffice 5.6.0.4 on a debian machine, and this bug is definitely still present.  I have also attached a spreadsheet demonstrating the bug, using the example from the Microsoft documentation quoted in my previous comment.

I do hope someone will look into this, since it would be great if folks could safely use libreoffice to read files generated in excel.  (As I mentioned before, this caused me to misread accounting files, which seems sort of important.)
Comment 9 Michael Meeks 2012-08-15 11:07:21 UTC
thanks for re-checking and the nice test case :-)

http://cgit.freedesktop.org/libreoffice/core/tree/sc/source/core/tool/interpr1.cxx#n4887

is prolly the code associated with this; patches are really very much welcome.
Comment 10 David Roundy 2012-08-16 22:04:53 UTC
The challenge with solving this problem (I'd think) is that you don't want to break *anyone's* spreadsheet.  So unless there is a simple way to tell that you're looking at an Excel spreadsheet (or one that was converted from Excel?), it's hard to know what sort of wildcards to use.

Writing code to handle MSO wildcards wouldn't be hard, but figuring out when to use them seems to me to be very hard.  But maybe there is already some sort of tracking of where files originated? It's still a hard user-interface problem, since users are liable to be confused if the same formula behaves differently in two different spreadsheets...

I think it's an important bug to fix, but am afraid that it may be very hard to find a reasonable option for fixing it.  :(
Comment 11 m.a.riosv 2013-03-07 23:50:40 UTC
*** Bug 61990 has been marked as a duplicate of this bug. ***
Comment 12 Björn Michaelsen 2013-10-04 18:47:48 UTC
adding LibreOffice developer list as CC to unresolved EasyHacks for better visibility.

see e.g. http://nabble.documentfoundation.org/minutes-of-ESC-call-td4076214.html for details
Comment 13 Robinson Tryon (qubit) 2013-10-19 00:24:18 UTC
Removing comma from whiteboard (please use a space to delimit values in this field)
https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Whiteboard#Getting_Started
Comment 14 Eike Rathke 2013-11-05 12:14:47 UTC
Removing entirely from EasyHack, it's not. Change needs to implement the actual wildcard search algorithm, add it to TextSearch, adapt the formula interpreter to support it and read/write the attribute from/to ODF.
Comment 15 GerardF 2014-05-10 07:05:36 UTC
*** Bug 78516 has been marked as a duplicate of this bug. ***
Comment 16 GerardF 2014-05-10 07:06:38 UTC
Change to "ENHANCEMENT".
Comment 17 Goran Rakic 2014-10-20 11:24:30 UTC

*** This bug has been marked as a duplicate of bug 72196 ***