[Solved] Option - Calculation - partially lost XLS

[nekrub2]

Dear all

I use Calc for database functions such as DSUM. Some of my criteria are "begin with" xxx*. Under openoffice file format I get the right result when setting in the options "enable regulars expressions in formulae". However, when I save the document as xls and later reopen it, this option is ticked off.

This is a bit cumbersome. Any clue what I can do to have this option always active, irrespective of the format?

sample

• A        B               C            D          E
1 Label1   Amount          Criteria     Label1     Amount     
2 14111    50                           141.*      70
3 14135    20

Formula: =DSUM(a1:b3;d1;d1:d2) expected amount = 70

[RusselB]

Welcome to the Forums.
It sounds like you have encountered, yet another, discrepancy between OpenOffice and MS Office.
The .XLS format is, despite popular opinion, not a true international standard, as the encoding sequences for the .XLS format files from back in Windows 3.1 days are different from those used today.
It is best to save your work using the Open Document Format, which uses the .ods extension. You can look at this tutorial to see the differences between the Open Document Format(s) and the MS Office format(s). While the tutorial is based on Writer vs. Word, the same principles apply to Calc vs. Excel.

If you want to ensure proper results, save the file as a .ods file. MS Office after, I believe, 2007, will work with the .ods format.

The Enable Regular Expressions setting might not be saved in the .xls format, in which case it's impossible to force the setting to be saved.

[Villeroy]

Excel does not support regular expressions in formulas.
OpenOffice is a dead project. Install https://libreoffice.org/ instead. It offers more Excel compatibility, including wildcards instead of regexes, most of Excel's formula syntax and xlsx. However, you should always save in the native file format of the application you are using. Do not use LibreOffice as a univeral document loader. Sooner or later it will hurt you.