Formulas like SUMIF and COUNTIF support in Excel Wildcards (like * and ?) and in ODF regular expression (pregex). ODF 1.2 defines to support both and switch via a config-option between none/wildcard/regex. LibreOffice needs to support wildcards to be compatible with XLS/XLSX and with ODF 1.2. See related bugreport in OpenOffice.org (from 2005, wo pre-fork time and still applies to LO and AOO): https://issues.apache.org/ooo/show_bug.cgi?id=32344 Related section in the ODF 1.2 specs: http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1017868_715980110 Operating System: All Version: unspecified
@Winfried: any chance you can confirm this one? Thanks for your expertise, Joren
Some details how to reproduce (sry, don't have msexcel at hand atm to produce+attach xls/xlsx): Sheet: A B 1 test1 11 2 test2 22 Formula: =SUMIF(A1:A2, "test*", B1:B2) Result in OO/LO: 0 Expected as shown in Excel which only knows about the wildcard-mode: 33 Now change the formula in LO to (note that "*" becomes ".*"): =SUMIF(A1:A2, "test.*", B1:B2) and active the ODF regexp-mode in Tools=>Options=>LO Calc=>Calculation=>Enbable regular expressions in formulas. 33 is displayed. Problems: 1. regex vs wildcards where LO only supports regex. 2. when importing Excel xls/xlsx users need to manually change wildcards to regular expressions. 3. after done users need to manually enable regex in formulas in the options. 4. on export to xls/xlsx users need to manually change regex back to wildcards else Excel will produce different results cause it only supports wildcards and not regex. 5. Incompatible with any other ODF producer/consumer that supports HOST-USE-WILDCARDS. Many problems. Point 2 is a real deal-breaker cause it makes it impossible for Excel spreadsheet-producers to also cover LibreOffice (eg by runtime-determination in a sheet if it runs in LO and then use regex in formulas rather then wildcards) without user-interaction. Since there are no alternates to functionality like SUMIF+wildcards and since lot of basic formulas are affected lot of use-case scenarios cannot be done in a compatible way :-/
Meaned point 3 is the deal-breaker since workaround impossible whereas when LO at least enables regex-mode on xls/xlsx-import spreadsheet-authors could work around. The ideal and 100% compatible that not needs any workarounds solution would be to just implement support for wildcards and enable wildcard-mode on xls/xlsx-import.
(In reply to comment #1) > @Winfried: any chance you can confirm this one? > > Thanks for your expertise, > Joren @Joren, AFAICS, the support of wild cards in calc formulas is not yet implemented in LibreOffice. I have no access to Excel, so I can't confirm that an Excel document with wild cards in formulas is not processed as one would expect. Given that Eike has been added to the CC, Eike already was involved with the OOo bug report mentioned in comment 1 and Eike is the real expert on this, I leave further commenting to Eike.
Well, what shall I say.. the "simple" wildcards are not implemented. Means they have to be implemented at utl::TextSearch, a Calc option to be introduced similar to the regular expressions option under Tools->Options (mutually exclusive), an option in the Find&Replace dialog (mutually exclusive), the ScQueryParam needs to transport it (similar to bRegExp), ScTable::ValidQuery() needs to support it, queries at ScMatrix need to support it, reading the setting from / writing to ODF files needs to be implemented and enabling the Calc option when importing Excel files. I probably forgot something.
Added support for none/regex/wildcards to Calligra Sheets with https://projects.kde.org/projects/calligra/repository/revisions/a63d4e13fe7ce54c8a751e06c8e6eab424cb6377 That means there is at least one ODF producer out there now writing ODS files using HOST-USE-WILDCARDS formulas not proper evaluated by LO/AOO.
*** Bug 38125 has been marked as a duplicate of this bug. ***
(In reply to Winfried Donkers from comment #4) > I have no access to Excel, so I can't confirm that an Excel > document with wild cards in formulas is not processed as one would expect. I just got hit by this - actually, I noticed the same bug in CPAN's Spreadsheet::ParseExcel, then opened the document in LibreOffice to check what's going on and looked at a completely different calculated tax sum as compared to Excel 2010.
*** Bug 60345 has been marked as a duplicate of this bug. ***
I started to work on this.
(In reply to Eike Rathke from comment #10) > I started to work on this. Good news. In my opinion, fixing this bug is worth a new point release of LO.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4c4976f717b3ddce68a872dffc2079ae49c41616 SearchAlgorithms2, SearchOptions2, XTextSearch2, TextSearch2; tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=599f0cbe1ddc1d54828489b389b78fdffa4ce39f interface to new XTextSearch2 with SearchOptions2, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f1a4663c819bf698f95a75b5a3319506c66f2778 implement '*' '?' '~' wildcard search, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8fd8a8875def353db34c68ee74d40772b83bae4c wildcard search unit tests, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=92173313eae76d721580120b0de2c98fd83c5384 prepare a SearchType detection fragment, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=06fa2a521b49afe44a387dbfcb65198382f6c57a change all bool bRegExp to utl::SearchParam::SearchType enum, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f7714ad62396d7ebc474989b598f9fb6b39afae3 add "use-wildcards" XML_USE_WILDCARDS, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=fb3f66c20f79f178ba0f53b850ed0af0d1c5b2ff add ConvertToSearchType() ConvertToBool(), tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=da6d813222e81470fac38282d1cebd1f858ba925 add [optional, property] boolean Wildcards, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ec3bc95a1cc8283867d2d4df4aa1268588ccaedf add Wildcards configuration option, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=91dd7a67d1b64d56fc00b19db102731a91f220f7 add FormulaWildcardsEnabled to ScDocOptions, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9f0fa7e5316509d73e3384a3b23b61a8a2d27f7c add Wildcards UNO API implementation, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=20fec9becf33e51a1d3ae8afcd52e4d65b343b7a import Wildcards at ScXMLCalculationSettingsContext, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ef02de2698d90fd874bddf3146165cbe85487bc5 write ODF table:use-wildcards to calculation settings, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=59a175b692fd26200bf2dfbe411a969eef3e00f5 read ODF table:use-wildcards, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1684e347cd16ce91ecf27d94691b185a0110cc34 add missing IsFormulaWildcardsEnabled() calls, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1b6b4ffbd9608eff245deb87da5f193f5d955e51 implement wildcards precedence at ScDocOptions, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=49d289475167ab21682bc8dbf26a7f67d5902ded let ConvertToSearchType() also adapt the regex bool, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b3a66ccdd3f4098670b593883602093c4a9e712c include '~' escape character in MaybeWildcard(), tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=bad266fa06294f1dacec11ec02dfc6ae4ec8cdc4 ScQueryEntry::GetSearchTextPtr() with SearchType, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d3c79d019114b7786268a1e821f96554e54767c0 adapt places that call GetSearchTextPtr(), tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d098b76f3311ec1b1763dbcfc1561791a4a2945f change to DetectSearchType(), tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=20b5917966d931fc9cbb8dd24a957f80f95e64bc add wildcards to Tools->Options->Calc->Calculate dialog, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=faf7979144203d110111a2a4e80c7688a45cd538 enable wildcards when importing .xls, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=5eaab67daa3dc6e18fa3b901a9ec57b509733905 add Wildcards / PROP_Wildcards, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4eea29f79eca05e76bfe90aaa176f39dd0fd80ea enable wildcards when importing .xlsx|.xlsb, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/help/commit/?id=9f7b898faa8a58c8489cdfaa36a9445497ad5751 add help content for wildcards option, tdf#72196
Let's call this implemented.
I would have rather seen radio bouton group like this : Use of Wilcards/Regex in formula ○ Wilcards ○ Regular expressions ○ None
Can do..
Eike: Sorry, this may be a stupid question: Does your changed import also work on .xlm and .xlsm Excel files?
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=63e914719e7f8a0b75810ef6fa98c43a9bffe085 replace wildcard/regex checkboxes with radio button group, tdf#72196 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Frank Fuchs from comment #42) > Does your changed import also work on > .xlm and .xlsm Excel files? Whatever .xlm might be, if it is imported as Excel file then yes, as is .xlsm for the functions that use wildcards. However, if you were referring wildcards used in VBA calls (assuming because you were asking about .xlsm) these were not changed.
Dear QA Team, the "fix" for this bug (or rather the new feature) is incorrectly listed as being part of LO 5.1.1.3, see: https://wiki.documentfoundation.org/Releases/5.1.1/RC3 As far as I can see, the fix has only been implemented for LO 5.2 and was not backported to LO 5.1. Can someone please correct the LO 5.1.1 RC3 release notes (according to that page, Christian Lohmaier might have produed those release notes). I apologize for adding this remark here, but I have no idea how to contact the right people responsible for the LO release notes.
Done. Some commits to help content may have triggered that, the list is generated, apparently from a misaligned tag.