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.
[Solved] Cell format changing on Save
[Solved] Cell format changing on Save
Last edited by MrProgrammer on Sat Oct 16, 2021 10:53 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
Open Office 3.3.0 Windows 7
Re: Cell format changing on Save
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.
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.
AOO4/LO5 • Linux • Fedora 23
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Cell format changing on Save
+1acknak wrote: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.
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.
Apache OpenOffice 4.1.1
Windows XP
Windows XP
- MrProgrammer
- Moderator
- Posts: 4904
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Cell format changing on Save
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
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:
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.
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>
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 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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Cell format changing on Save
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.
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 youcan'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
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.
Edit: PS: |
Mr Proggy's suggestion, =TEXT(cell;"0.0#") is probably the best workaround ( ), although it will require some changes. Just be aware that you
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>
Edit: PPS: |
AOO4/LO5 • Linux • Fedora 23
- MrProgrammer
- Moderator
- Posts: 4904
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Cell format changing on Save
acknak wrote:I have no idea what that "decimal-replacement" property is supposed to do.
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 "".ODF specification section 19.346 wrote: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.
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".acknak wrote:The only difference is that any integer will be shown without any decimal digits.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Cell format changing on Save
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.
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.
Open Office 3.3.0 Windows 7
- MrProgrammer
- Moderator
- Posts: 4904
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Cell format changing on Save
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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).