If I attempt to assign the following format code:
[BLACK][=0]"(all fast) ";[BLACK][=1]"(fast adj)";[BLACK][=2]"(races)";"(non-CD)"
it fails (and clicking the checkbox shows that everything starting with [BLACK][=2] is wrong), but this works:
[BLACK][=0]"(all fast) ";[BLACK][=1]"(fast adj)";"(races)"
It's documented in https://help.libreoffice.org/Common/Number_Format_Codes that 3 is the maximum number. It may be, among other reasons, that there are interoperability reasons for this (i.e. that it couldn't be exported to an Excel file with more than three).
There is another way to do what you're trying to do, although it's slightly more long winded:
1. Create four styles for your four cases, so that for instance the first one has a name of '(all fast)' and a number format code of "(all fast)" <-- including the double quotes
2. Select the range you want to have the style apply to
3. Open the menu Format - Conditional Formatting - Condition...
4. Add four conditions:
Condition 1: Cell value is, equal to, 0; Apply Style (all fast)
Condition 2: Cell value is, equal to, 1; Apply Style (fast adj)
... and so on
There also isn't a convenient way to name this whole set of conditions, but you can copy and then "Paste Special" with "Formats" selected to move them around
As explained in comment 1, LibreOffice only supports 3 formats and this is documented.
That being said - this is a fine enhancement request, marking as such.
Note: Enhancement requests are done almost exclusively by volunteers, that being said they choose (ie. we don't push) what enhancement they want to do. So despite this being a valid request - it's dependent on a volunteer choosing to implement and therefore there is no ETA. Patches are welcome if you or someone you know wants to implement this.
In the event, I found another, better way of doing this, using Data->Validity with a pick list.
As for LibreOffice 220.127.116.11: Still the same problem.
My case: Make huge numbers "human readable" with a selectable amount of digits since there is no template for that.
Working (German install) show at least two, at max four digits:
[>=10000000]#.. "Mil";[>=10000]#. "Th";Standard
(Libreoffice always adds "Standard" at the end, probably "Default" in English installations)
[>=10000000000]#.. "Bil";[>=10000000]#.. "Mil";[>=10000]#. "Th";Standard
Also not working:
[>=10000000000000]#.. "Tri";[>=10000000000]#.. "Bil";[>=10000000]#.. "Mil";[>=10000]#. "Th";Standard
I'll try the workarounds described in this bug report now, I guess they will work. Thanks for the hints.
Another way that works is to use conditional formatting with the conditional formula being STYLE(...). As far as I'm concerned, this can now be closed.
I think it's still a valid request so going to leave it open. Shouldn't require all these tricks to use more than 3 conditional formats :) That being said, it's good to know there are a couple ways to work around the issue.
The number format code structure needs to be interoperable with Excel and other spreadsheet implementations that use the same. Expanding the number of conditions is not possible. Use conditional formatting with different number formats if such is needed.
(In reply to Eike Rathke from comment #7)
I perfectly understand the reasoning here. Still, I agree with comment 6; and I feel that limiting ourselves for compatibility reasons is too ... limiting. Except for the compatibility, there seems to exist nothing limiting the possible number of subformats with conditions, so having arbitrary number of conditional subformats, plus one subformat without condition (for all other numerical values), plus one for text, seems like unambiguous superset of existing syntax; and for export to foreign formats like XLS(X), it looks reasonable to just drop the excessive subformats, so that only two first conditional subformats plus unconditional plus textual are kept. In the end, we don't limit ourselves to 256 columns just to keep compatibility with XLS?
I agree with #8. There's no obvious reason why LibreOffice must confine itself to being an exact match for Excel.
If I attempt to save a spreadsheet in .xlsx format, I already get an alert that the document might contain formatting or content that can't be saved in that format, without specifying what that format or content is. This would simply be an example of such. I have no interest in whether this spreadsheet will work in Excel, and this would be a useful superset capability.