Created attachment 71686 [details]
LibO: Version 184.108.40.206.alpha0+ verified on 4.0 Beta1
The new "contains" feature for conditional formatting doesn't behave as expected unless you add quotes. If you add a conditional format without the quotes, no cells are triggers as "meeting criteria" which is incorrect, but if you add quotes around the text the behavior is correct.
Another point is that if you go back in to edit your conditional format you can see that the previously entered word without quotes now have single apostorophe's ('WORD'), this should probably be "WORD" and the problem would be resolved.
Example document, column A shows conditional formatting without quotes behaving incorrectly, column B shows with quotes and working as expected.
So this one is quite tricky. I have to think a bit about it.
The problem is formula input vs string input. How to decide when the input is a string and when it is a formula.
Moving to normal as this is not as bad as original thought. To some extend the current behavior is consistent with the other conditional formats.
I need to check some corner cases and how they are handled.
I'd argue that text is more likely with "contains" than numbers, so if there is no check, it should default to "" instead of ' ', but really, a check should probably be implemented somehow.
Another option is having "contains (text)" and "Contains (number"
That's a workaround at least temporarily perhaps
(In reply to comment #3)
> I'd argue that text is more likely with "contains" than numbers, so if there
> is no check, it should default to "" instead of ' ', but really, a check
> should probably be implemented somehow.
It is not that easy. It is not number vs. text. It is that we can input formulas there too. Everywhere we can input formulas you need to escape strings with quotes. This is also true for all other conditional formats.
> Another option is having "contains (text)" and "Contains (number"
> That's a workaround at least temporarily perhaps
That is not an option.
Misleading behaviour also raises when "equal to" or "not equal to" operands are used.
OS: Windows 7
(In reply to comment #5)
> Misleading behaviour also raises when "equal to" or "not equal to" operands
> are used.
> OS: Windows 7
This is not misleading behavior! This is absolutely correct for equal. It is questionable if it is the best behavior for contains.
Please note that this input field also accepts formulas and ranges which means that we have to escape strings with quotes as in all the other formula places.
my bad for opening the door on this one ;) Thanks for clarifying about what's going on with this, good luck trying to find a solution :) I do hope that we don't face having to explain to users why their conditional formatting isn't working with contains (because they fail to put in " "). Thanks again Markus, I'll be discussing other CF issues with you in the coming days
Ok after looking at Excel I think I'll rename them to: Contains Text, Not Contains Text, Begins with Text, Ends with Text and don't try to interpret the input as a formula.
Thanks for the update :)
Reproducible with LO 220.127.116.11 (Win 8.1)
Fixed in master by showinga warning when the string is interpreted as a column label. Hopefully we can even disable column labels by default for LibreOffice 5.0.