![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Hi,
I need, of course, formulars to be case sensitive. This can be set in the options for calc. The option is not saved permanently and every time I open a spreadsheet, I need to set the option again.
How can I save this option permanently so I don't need to set it over and over again?
To clarify: I need the string comparisons of IF() to be case sensitive: 'if(a1 = "x"; ...) is, of course, different from 'if(a1 = "X"; ...)'.
Why would anyone make them not case sensitive unless explicitly specified, and how do I specify case sensitiveness per formula?
To make a formula that is case sensitive, you should be able to use the string functions (I think) to convert to a known case before performing the comparison, e.g. if(UPPER(A1) = "X";...)
/Gary
Sorry, that's to make a formula NOT case sensitive./G.
They seem to be case sensitive by default. I've just tried if (B1 = "x";5;3)and it returns 5 when B1 = "x" and 3 when B1 = "X".
To make a formula that is NOT case sensitive, you should be able to use the string functions (I think) to convert to a known case before performing the comparison, e.g. if(UPPER(A1) = "X";...)
/Gary
I need, of course, formulas to be case sensitive. This can be set in the options for calc. The option is not saved permanently and every time I open a spreadsheet, I need to set the option again.
I'm surprised by that. If you mean the options at Tools | Options...
LibreOffice Calc | Calculate | Case sensitive, doesn't this apply
per user and thus stick not only between documents but between LibreOffice sessions?
How can I save this option permanently so I don't need to set it over and over again?
If it doesn't stick - and if this option is, as I imagine, saved in the user profile - you might want to wonder if your user profile has become corrupt. If you close LibreOffice and delete or rename the profile, LibreOffice will create a new, clean one next time you start it. If you then set the option again, does it now stick?
To clarify: I need the string comparisons of IF() to be case sensitive:
'if(a1 = "x"; ...)' is, of course, different from 'if(a1 = "X"; ...)'.Why would anyone make them not case sensitive unless explicitly specified, ...
I suspect those who prefer the other option would ask the opposite question! If you are looking for references to dogs, you might want to find "Dogs are friendly pets" as well as "My dog's got no nose".
... and how do I specify case sensitiveness per formula?
If the option is, as I suspect, per user, your spreadsheet will go wrong if you need to change the option for another document, if a different user handles it on the same computer, or if you exchange it with someone using a different computer. So you are quite right to identify that building the requirement into your formulae and not relying on options is the reliable way to go. And it's quite easy: the EXACT() function comes to your aid. Instead of using
=IF(A1="X";...)
use
=IF(EXACT(A1;"X");...)
This will match "X" but not "x" irrespective of the option setting.
I trust this helps.
Brian Barker
I need, of course, formulas to be case sensitive. This can be set in
the options for calc. The option is not saved permanently and every
time I open a spreadsheet, I need to set the option again.I'm surprised by that. If you mean the options at Tools | Options... |
LibreOffice Calc | Calculate | Case sensitive, doesn't this apply per
user and thus stick not only between documents but between LibreOffice
sessions?
No, this option is not remembered.
How can I save this option permanently so I don't need to set it over
and over again?If it doesn't stick - and if this option is, as I imagine, saved in the
user profile - you might want to wonder if your user profile has become
corrupt. If you close LibreOffice and delete or rename the profile,
LibreOffice will create a new, clean one next time you start it. If you
then set the option again, does it now stick?
I have seen this on different computers, with both the Linux and the Windoze version of LO.
To clarify: I need the string comparisons of IF() to be case sensitive:
'if(a1 = "x"; ...)' is, of course, different from 'if(a1 = "X"; ...)'.Why would anyone make them not case sensitive unless explicitly
specified, ...I suspect those who prefer the other option would ask the opposite
question!
Things are usually case sensitive, unless you specify otherwise. It is what I expect.
If you are looking for references to dogs, you might want to
find "Dogs are friendly pets" as well as "My dog's got no nose".
I don't like dogs ...
... and how do I specify case sensitiveness per formula?
If the option is, as I suspect, per user, your spreadsheet will go wrong
if you need to change the option for another document, if a different
user handles it on the same computer, or if you exchange it with someone
using a different computer.
I haven't tried with different users.
So you are quite right to identify that
building the requirement into your formulae and not relying on options
is the reliable way to go. And it's quite easy: the EXACT() function
comes to your aid. Instead of using
=IF(A1="X";...)
use
=IF(EXACT(A1;"X");...)
This will match "X" but not "x" irrespective of the option setting.
Thank you, maybe that works; I'll try it tomorrow.
That would make the formula case insensitive.
All spreadsheets had been case insensitive ever since.
Use the EXACT function for case sensitive comparison.
The calculation options do not apply to Excel files which is why you
have to reset this option every time you load the file. The Excel file
format has no place where this setting can be stored.
Hi,
I need, of course, formulars to be case sensitive. This can be set in
the options for calc. The option is not saved permanently and every
time I open a spreadsheet, I need to set the option again.How can I save this option permanently so I don't need to set it over
and over again?To clarify: I need the string comparisons of IF() to be case sensitive:
'if(a1 = "x"; ...) is, of course, different from 'if(a1 = "X"; ...)'.Why would anyone make them not case sensitive unless explicitly
specified, and how do I specify case sensitiveness per formula?All spreadsheets had been case insensitive ever since.
Use the EXACT function for case sensitive comparison.The calculation options do not apply to Excel files which is why you
have to reset this option every time you load the file. The Excel file
format has no place where this setting can be stored.
"All spreadsheets had been case insensitive ever since. " - I don't understand this.
The OpenFormula specification says that comparisons are case sensitive unless HOST-CASE-SENSITIVEisfalse.
>>>
6.4.7Infix Operator "="
Summary:Report if two values are equal
Syntax:ScalarLeft =ScalarRight
Returns:Logical
Constraints:None
Semantics:Returns TRUE if two values are equal. If the values differ in type, return FALSE. If the values are both Number, return TRUE if they are considered equal, else return FALSE. If they are both Text, return TRUE if the two values match, else return FALSE. For Text values, if the calculation settingHOST-CASE-SENSITIVEisfalse, text is compared but characters differencing only in case are considered equal. If they are both Logicals, return TRUE if they are identical, else return FALSE. Error valuescannotbe compared to a constant Error value to determine if that is the same Error value.
I've never seen any spreadsheet program (Lotus 1-2-3, Excel, Gnumeric,
Calc) with case sensitive string comparison.
But all of them have an EXACT function to enforce case sensitivity.
I need, of course, formulas to be case sensitive. This can be set in the options for calc. The option is not saved permanently and every time I open a spreadsheet, I need to set the option again.
I'm surprised by that. If you mean the options at Tools | Options... | LibreOffice Calc | Calculate | Case sensitive, doesn't this apply per user and thus stick not only between documents but between LibreOffice sessions?
No, this option is not remembered.
I've done some research, and the setting is stored in registrymodifications.xcu in the profile. So yes: it *will* be saved per user, not per document or per system, and will stick across LibreOffice sessions. (But perhaps not, as I suggested, if you have a corrupt profile.)
How can I save this option permanently so I don't need to set it over and over again?
If it doesn't stick - and if this option is, as I imagine, saved in the user profile - you might want to wonder if your user profile has become corrupt. If you close LibreOffice and delete or rename the profile, LibreOffice will create a new, clean one next time you start it. If you then set the option again, does it now stick?
I have seen this on different computers, with both the Linux and the [Windows] version of LO.
If you use different systems, the setting would need to be made in your profile separately on each, of course.
To clarify: I need the string comparisons of IF() to be case sensitive:
'if(a1 = "x"; ...)' is, of course, different from 'if(a1 = "X"; ...)'.Why would anyone make them not case sensitive unless explicitly specified, ...
I suspect those who prefer the other option would ask the opposite question!
Things are usually case sensitive, unless you specify otherwise. It is what I expect.
I'm sorry you missed my point. I understood what you wanted. Anyone reading your enquiry will have understood what you wanted. My point was that others might prefer the existing arrangement. I think others should be given consideration.
If you are looking for references to dogs, you might want to find "Dogs are friendly pets" as well as "My dog's got no nose".
I don't like dogs ...
Did you imagine I was writing for your benefit alone? There are plenty of dog-lovers on the list.
... and how do I specify case sensitiveness per formula?
If the option is, as I suspect, per user, your spreadsheet will go wrong if you need to change the option for another document, if a different user handles it on the same computer, or if you exchange it with someone using a different computer.
I haven't tried with different users.
Actually, it appears you have. You say you have experience of this matter on different operating systems. Even though you are the same person on each, you will be separate users and have separate profiles on the different systems. It is precisely because you may want to move documents in this way that it is far better to control what happens in your formulae and not to rely on option settings.
So you are quite right to identify that building the requirement into your formulae and not relying on options is the reliable way to go. And it's quite easy: the EXACT() function comes to your aid. Instead of using
=IF(A1="X";...)
use
=IF(EXACT(A1;"X");...)
This will match "X" but not "x" irrespective of the option setting.Thank you, maybe that works; I'll try it tomorrow.
Oh, it does work for others. I'm not sure how your use if it downgrades this to "maybe".
Brian Barker
profile.)
Can I interject a comment and an experiment here?
Firstly, a global per-user setting for case sensitivity would be a recipe for disaster, and for spreadsheets not being properly portable.
But it isn't like that.
Now, checking under Tools|Options....Calculated showed my system (4.4.3.2/Mint 17) had the 'case sensitive' option' set. (TBH, I'd never noticed this before, but then I hardly ever use spreadsheets.)
I made a simple spreadsheet: two cells contained 'AAA' (A1) and 'aaa' (A2), and two cells with =IF(A1="AAA", 1, 0) and =IF(A2="AAA", 1, 0) and displaying 1 and 0 as expected. Save this as doc1. Set the Tools case sensitivity to no. The results cells now show 1 and 1 of course; save as doc2, and exit LO.
Now, if I reload doc1 and doc2 simultaneously, showing in 2 windows, both spreadsheets show the same results as before. Furthermore, checking the (expected-to-be-global) Tools option for case sensitivity shows different values when the dialogue is opened from each window.
Again, checking the contents.xml for each file shows an extra entry
<table:calculation-settings table:case-sensitive="false"/>
for the 'case insensitive' file. (Presumably the default is 'true'.)
So as one would hope, the setting is saved per-file.
My suspicion is that any value in registrymodifications.xcu is simply a default for new documents.
(I've communicated privately with Brian before posting the above, and I believe he has some extra germane comment to add.)
Indeed: but basically only to say that everything you say is correct! My apologies to all for the misdirection.
Another couple of bits of evidence that the case-sensitive option is a property of the document:
o If you start a new spreadsheet but close it immediately, you are not challenged to save it or discard it, of course, as it not been modified. But if you start a new spreadsheet and then just change the case-sensitive option, you *are* challenged, confirming that this is indeed a change to the document.
o If you open more than one spreadsheet at the same time which happen to have the same case-sensitive setting, changing the setting affects only the document in which you make the change.
Another contributor suggested that the original enquirer's problem may have been caused by (unwisely) saving spreadsheets in Microsoft Excel format, and it appears that this may well have been so. My experiments suggest that spreadsheets saved with the option set differently are displayed correctly in the Excel Viewer (and therefore presumably in Excel itself) but do not render correctly when reopened - suggesting that it is LibreOffice's input filter that is at fault. Solution: save in .ods format.
But the bottom line is still the same: because the situation is so complicated and fluid, the reliable way to deal with the problem is to build case sensitivity into formulae, testing similarity using the EXACT() function instead of the "=" operator.
Apologies again for the (temporary) distraction.
Brian Barker
Now, if I reload doc1 and doc2 simultaneously, showing in 2 windows,
both spreadsheets show the same results as before. Furthermore, checking
the (expected-to-be-global) Tools option for case sensitivity shows
different values when the dialogue is opened from each window.Again, checking the contents.xml for each file shows an extra entry
<table:calculation-settings table:case-sensitive="false"/>
for the 'case insensitive' file. (Presumably the default is 'true'.)So as one would hope, the setting is saved per-file.
Thank you for testing this!
I wouldn't hope that because this setting appears among others which are (or seem to be) global (or defaults or the desired settings, however you might call it), with no indication that this particular setting is per spreadsheet.
My suspicion is that any value in registrymodifications.xcu is simply a
default for new documents.
How are we supposed to know which settings made at Tools->Options are global and which aren't? I would expect all of them to be global and to be remembered between sessions like every other software does in such context.
Another contributor suggested that the original enquirer's problem may
have been caused by (unwisely) saving spreadsheets in Microsoft Excel
format, and it appears that this may well have been so. My experiments
suggest that spreadsheets saved with the option set differently are
displayed correctly in the Excel Viewer (and therefore presumably in
Excel itself) but do not render correctly when reopened - suggesting
that it is LibreOffice's input filter that is at fault. Solution: save
in .ods format.
The spreadsheets are created with a perl module (Excel::Writer::XLSX) because there doesn't seem to be an equivalent module to create ODS files. Saving them as ODS is not an option because it's annoying having to switch between different formats and to create various copies of the files all the time.
Even saving them as CSV to import them is a step that needs to be removed from the workflow.
But the bottom line is still the same: because the situation is so
complicated and fluid, the reliable way to deal with the problem is to
build case sensitivity into formulae, testing similarity using the
EXACT() function instead of the "=" operator.
I don't understand why or how this is complicated. When I make a general/global setting amongst others, I simply expect this setting to be in effect regardless which document I open.
If this setting cannot be made general/global, then it needs to be clear that it is per document --- not considering that some ppl might nonetheless wish that a global/general setting for this exists. And if a setting which is per document overrides a general/global setting, I should optionally receive a warning.
......
The spreadsheets are created with a perl module (Excel::Writer::XLSX)
because there doesn't seem to be an equivalent module to create ODS
files. .....
That's possibly because you don't really need one. Or at least, it's easy enough to create the necessary xml by hand. Easiest way is to create a document like the one you want, and examine its structure, then make your code write something similar. Some quick work with perl's zip-file modules, and you're done. I've a number of programs that do just this.
........
I don't understand why or how this is complicated. When I make a
general/global setting amongst others, I simply expect this setting to
be in effect regardless which document I open.If this setting cannot be made general/global, then it needs to be clear
that it is per document --- not considering that some ppl might
nonetheless wish that a global/general setting for this exists. And if
a setting which is per document overrides a general/global setting, I
should optionally receive a warning.
I sympathize. IMO the GUI is badly designed (iow broken), and the case sensitivity might better appear somewhere in the File|Properties dialogue. I'll not hold my breath though.
All of Calc's general settings are stored globally.
All calculation settings are stored in the ODF spreadsheet. Excel does
not have any such setting so neither your Perl script nor MS Excel can
store any such setting anywhere when using Excel file formats.
The default setting for foreign file formats is case-insensitive since
this is what spreadsheets use to be since 30 years.
All Excel support forums recommend the EXACT function for case sensitive
string comparison. It is very easy to write EXACT(A1,A2) instead of
A1=A2 which works with all supported spreadsheet formats.
It's not so much where they're stored, as the access route. This particular setting - case sensitivity - is stored in the document, but accessed in a dialogue that one might expect to be concerned with global settings.
There seems to be a similar issue with "precision as shown" on the same settings page. Not that I, personally, would ever, ever countenance that as an available option in the first place.
For the Excel documents discussed here there is no way to store anything
at all because Excel does not know any such setting. The issue with
"precision as shown" is the same issue as with any other setting on that
options page. All the calculation settings are document specific to all
ODF spreadsheets. The options dialog needs some way to indicate which
settings are stored in the document and which ones are stored in the
user profile.
The solution to this topic for all kinds of spreadsheet documents is the
EXACT function.