[Solved] Cell format changing on Save

[Courtenay]

I formatted some cells with the following format:
[>0]0.0#;[<0]0.0#;0
This did just what I wanted: Zero printed as 0, all non-zero numbers had one decimal place, and numbers with two decimal places printed with two decimal places. I saved my spreadsheet to an .ods file and closed it. When I later opened it, the format had changed to:
[>0]0.00;[<0]0.00;0
Which is not what I wanted or what I entered (It prints all non-zero numbers with two decimal places.)

This behavior repeated each time I saved, closed, and opened the file.
Why is saving and opening a file changing the format, and is there any way I can stop this behavior?

Thank you.

[acknak]

I see the same thing.

Looks like a bug to me--there are other reports of lost/changed formats, but I don't see this particular one.

I don't see any workaround; maybe someone else has an idea for some alternative.

Looking at what gets saved in the .ods file, this may not even be fixable. A custom format as simple as 0.0# gets changed to 0.00 on reloading the file.

[Charlie Young]

I see the same thing.

Looks like a bug to me--there are other reports of lost/changed formats, but I don't see this particular one.

I don't see any workaround; maybe someone else has an idea for some alternative.

Looking at what gets saved in the .ods file, this may not even be fixable. A custom format as simple as 0.0# gets changed to 0.00 on reloading the file.

+1

I spent some time on this, and I couldn't find anything reasonable. Could maybe use a macro with a listener, but that would obviously be overkill.

[MrProgrammer]

Hi, and welcome to the forum.

A saved ODS file must comply with the ODF standard. I would not presume to be an expert on this specification, but I believe that cell formatting is described with the properties in section 16.27 Data Styles. While these are quite flexible, they probably do not allow every possible Format Code to be replicated by the allowed ODF properties. Your 0.0# is represented as

Code: Select all

<number:number-style style:name="N116">
<number:number number:decimal-places="2" number:min-integer-digits="1"/>
</number:number-style>

As you can see, 0.0# is represented in the ODS file as "1 integer digit, 2 decimal digits" and becomes 0.00 when the file is opened. I suspect there is no combination of codes in 16.27 which allows 0.0# to be saved in the ODS file, and Calc is using the closest representation that complies with the ODF standard.

So, what can you do? Some ideas:

• Get used to the 0.00;0.00;0 format. Is it really so terrible?
• Use a style instead of direct formatting. Open the file and then reset this single style, thereby changing all the cells with that style.
• Use a format 0.00;0.00;0 with conditional format Formula is: A1*10=INT(A1*10) Style: XXX with format 0.0;0.0;0
• Use the =TEXT(cell;"0.0#") function. Calc can't change this "0.0#" when it saves the file because it's a text string.

If there really is a set of 16.27 specifications which would allow 0.0# to be represented, then you could Report this as a bug to the developers. But if there is no combination that allows 0.0# to be represented, your only hope would be to get the ODF standard revised first, because only then could the developers implement a fix. It is possible to get the standard changed — it's currently version 1.2 — but you would probably have to convince Apache that they want to go through that effort.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[acknak]

Normally, I wouldn't recommend this, but you might try saving as .xls instead of .ods. I suspect that the Excel format will store the number format string directly. If so, then it should be saved and restored properly.

Edit: PS:

Note: Using .xls format may well incur other problems, so I would still consider it only as a last resort.

Mr Proggy's suggestion, =TEXT(cell;"0.0#") is probably the best workaround ( ), although it will require some changes. Just be aware that you can't shouldn't use the formula's result as input to further calculations, because the result/value is text, not a number.

The odd thing is that the number format 0.# works just fine. And the xml translation of that format is

• <number:number-style style:name="N118">
    <number:number number:decimal-places="1" number:min-integer-digits="1" number:decimal-replacement=""/>
  </number:number-style>

I have no idea what that "decimal-replacement" property is supposed to do.

Edit: PPS:

Ok, tweaking the xml has provided another workaround. Instead of using 0.0# as the number format, use 0.##. That gives almost the same formatting (none, one or two decimal places, as needed) without requiring the conditional format, and it saves and reloads properly. The only difference is that any integer will be shown without any decimal digits.

[MrProgrammer]

I have no idea what that "decimal-replacement" property is supposed to do.

The number:decimal-replacement attribute specifies a replacement text for decimal places if a number style specifies that decimal places are used but the number displayed is an integer. Note: What replacement text is supported is implementation specific.

This is interesting. It looks as if one can potentially set number:decimal-replacement=" exactly" and have a value of 17.19 displayed as 17.19 while 17 be displayed as "17 exactly". I haven't tested to see how this property is actually implemented in Calc. I don't see a way to set it using the user interface — one would have to resort to editing the XML. It seems very possible that the only "implementation specific" value that's supported is "".

The only difference is that any integer will be shown without any decimal digits.

The OP could use a conditional format and test INT(cell)=cell if it's critical that integers be shown with one decimal, like 17.0 for the value 17. If there were a way to set the decimal-replacement property in the UI, one could in theory use number:decimal-replacement=".0".

[Courtenay]

Thank you for your replies.

It appears that calc is preforming to the standard, but that the standard is, well, odd. IMHO, if 0.0# can not be saved, it might as well not exist.

Using TEXT is not an option for me; the cells in question are user input to start the calculation. The odd formatting was designed to make it easier to catch user input errors.

0.## is as close as I am going to get to what I want, except that I note that the simple default "General" format behaves exactly the same way,
given that there are never more than two decimal places; that is what I am using now.

Again, thank you for responding.

[MrProgrammer]

Calc allows formats like 0.0# for compatibility with other spreadsheet programs. Unfortunately there is no easy way for users to know which user-defined formats can be saved in ODS format and which ones can't. Selecting any of the Calc-supplied formats should be safe.