-- Tested on MacOS Ventura 13.2.1 Libreoffice 7.5.1.2, build: fcbaee479e84c6cd81291587d2ee68cba099e129 -- To reproduce: Launch LibreOffice Click "Create/Calc Spreadsheet" Enter the following in cells A1-A3: 123.456 1234.56 12345.6 Select these three cells Click "Format/Cells..." In the "Category" list, click "User-defined" In Format code enter: "###.000 e0" ..but without the quotes Click the 'add' button (bearing the green checkmark) Note that: the "Category" switches to "Scientific" the new format appears as expected the example is formatted as desired (with a lowercase 'e' preceeded by a space) Under category, click "User-defined" Verify that the new format appears here as well. Click OK Verify that the three cells are formatted as desired: 123.456 e0 1.235 e3 12.346 e3 Click "File/Save," give the document a name, and click "Save" Close the file, then close Libreoffice Reopen the file. Note that the cells are now formatted as: 123.456E0 1.235E3 12.346E3 Click "Format/Cells" Note that the format has been altered to: ###.000E0" " Note that this altered format appears in both categories: "User-defined" and "Scientific" -- Expected result: Format should be saved as entered. Or, if the format is erroneous, it should be corrected _immediately_, rather than waiting until the document is saved/reopened. -- Documentation The documentation is not entirely clear on whether a space is acceptable before the "e" in this style format. (Though this reporter would appreciate it!) The documentation _does_ say that a lowercase "e" should be OK, but LibreOffice does not preserve it. -- similar bug? Bugs 146404 and 152724 seem very similar, though this reporter can't be sure it's the same underlying issue.
(In reply to Matt Keveney from comment #0) > Bugs 146404 and 152724 seem very similar, though this reporter can't be sure > it's the same underlying issue. CC'ing Laurent Balland, who has been reviewing several of these number format issues recently. Bug 146404 is about the custom [COLORnn] format. Bug 152724 is about the "_X" custom format, to leave a space equal to the width of that "X" character. There are other several bug reports related to supporting custom number format codes that are supported by other spreadsheet software. In this case, this seems to be related to space character(s) before the "E" (and/or "e") in the scientific number format. I am not sure whether this is also specific to the right-side of the number, or the decimal side of the number, or... The precise "definition" of the problem might depend on how exactly the code understands the use of the E/e for scientific notation, and whether this is allowed or supported in ODF. Two questions: A_ Have you tried testing with a dev. release or testing version? B_ Have you tried the same steps using other software (e.g. AOO and/or Excel for MacOS or any other) to see how the behavior is there?
> A_ Have you tried testing with a dev. release or testing version? > B_ Have you tried the same steps using other software > (e.g. AOO and/or Excel for MacOS or any other) to see how the > behavior is there? I just tried google sheets, since that was easiest. It does not accept format I reported above. Instead it says 'invalid format' right in the format editor, which would be a great thing to add to LibreOffice. I _could_ however enter the equivalent format: ###.000 e-0 This behaves as expected: https://docs.google.com/spreadsheets/d/1ecDo2_sJ_ag33H7Yf4VhVSoR60u6d24Z7XsgraPgaDc The format with the added '-' does not help in LibreOffice; again, it's replaced with ###.000E0" " on save/reopen. Aside: this format is just a stopgap. What I _really_ want is support for SI prefixes (k,M,G,T,P,m,µ,n,p,f) and so forth. I'll make an entry at https://ask.libreoffice.org/tag/feature-request... just thought I'd put a bug in your ear _here_, while I have your attention :-). Very useful for electronics calculations. Thanks!
added related feature request: https://ask.libreoffice.org/t/please-add-support-for-si-prefixes-in-number-formats/88844
> B_ Have you tried the same steps using other software > (e.g. AOO and/or Excel for MacOS or any other) to see how the > behavior is there? Just tested Apache OpenOffice 4.1.14. It has similar behavior, only worse! It does not recognize the three ### as signifying engineering units, so the test data listed above initially displays as: 123.456 e0 123.456 e1 123.456 e2 Then, after save/reload it looks like this: 123.456E+0 123.456E+1 123.456E+2 And the format code has been changed to this: 000.000E+0 If you place your cursor in the format code edit window, you can see that a _trailing_ space has been added to the format code, but no double-quote characters are visible as in LibreOffice. so... please don't use the current OpenOffice code as a source of inspiration :).
This bug about saving in ODF format has two parts: - format code is always treated as uppercase. So, scientific format is always restored as "E" and not "e". - Embedded text is not permitted in scientific format For this special format, only XLSX permits to preserve it. Sign for exponent in ODF was treated in bug 90133 and introduce in LibO since version 5.0.0
(In reply to Matt Keveney from comment #2) > Aside: this format is just a stopgap. What I _really_ want is support for > SI prefixes (k,M,G,T,P,m,µ,n,p,f) and so forth. I'll make an entry at > https://ask.libreoffice.org/tag/feature-request... just thought I'd put a > bug in your ear _here_, while I have your attention :-). Very useful for > electronics calculations. Thanks! SI prefixes request is filled in bug 90708. However, I am not so enthusiast with SI prefixes, as it fails with combined unit: 1000 m² is NOT 1 km² (see bug 90708 comment 7) But this actual request may be achieved with a new attributes to the format, containing a text replacement of scientific notation.
> But this actual request may be achieved with a new attributes to the format, > containing a text replacement of scientific notation. I'm not following you. Is this "text replacement" an _existing_ feature? ..or an alternate feature proposal? If the former, please point me to the documentation.
(In reply to Matt Keveney from comment #7) > > But this actual request may be achieved with a new attributes to the format, > > containing a text replacement of scientific notation. > > I'm not following you. Is this "text replacement" an _existing_ feature? > ..or an alternate feature proposal? > > If the former, please point me to the documentation. No, it was just an idea how to fix it. First of all, I planed to fix embedded text in scientific notation. See some progress here: https://gerrit.libreoffice.org/c/core/+/150804
Laurent Balland committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8ef4d3a4160af967937e9bb7c60bee4a8eb71a1b related tdf#153993 Embedded text in scientific numbers It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Laurent Balland committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/cc721291cc4a53503398b82dd86baa521b2b13cc tdf#153993 Extend ODF: lowercase for exponent char It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.