Bug 72196

@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.