Excel 365 - Custom Format Not Saving Correctly

Question

I made a xlsx spreadsheet, and formatted some number cells with custom format, which works fine in past excel, and appears to work when doing it on the current version. The format is as per the screenshot:

You will see that I am making it a number with "liters/d" after the number. And you will notice the cell is correctly showing "295.9 liters/d". The file is on the desktop and I left it on the screenshot on the left side.

So I save it, and no complaints from excel, and everything appears to be ok. But when I open the saved file, I get this dialog box:

So if I click no, then it comes up with a blank page. If I click yes, then the following is the next dialog box:

So I close the dialog box, and now my custom formatting is missing:

This repeats every time I try to save it. I have done this kind of formatting many times over the past, so this seems like something new, or MS did an update on Excel and perhaps I am missing something?

It works, but it just won't save properly, or it won't load it properly. I think that MS must have messed up an update, but maybe someone has an answer on how to do this?

Answer 1

I can't upload from this computer I'm afraid.

As a matter of interest, if you make the format code:

0.0" liters/d"

rather than

0.0 "liters/d"

does that help?

Answer 2

Can you provide a link for the file you got to work? I have repeated it several times and still get the same error.

Not sure which formats you are suggesting I remove? I didn't create any of those backslash ones, and there are a lot of them.

Not sure if this is helpful but ....

I have a subscription for Office 365 In Windows Apps, it says last updated August 27 --- two days ago. In apps it says 16.0.17928.20114 which I assume is the build or version number?

Answer 3

This is a bug, please report it to Microsoft:
How do I give feedback on Microsoft Office? - Office Support

Here is the bug marked in bold red:

If you apply that format in your versions the styles sheets contains this XML code:
<numFmt numFmtId="166" formatCode="0.0**/**"liters/d""/></numFmts>

In my "old" version if I do the same I get this XML code:
<numFmt numFmtId="166" formatCode="0.0**** "liters/d""/></numFmts>

Please also check your system for errors, maybe another program has installed an incorrect DLL in your system.
Repair an Office application - Office Support
System file check (SFC) Scan and Repair System Files & - Microsoft Community

Andreas.

Answer 4

As a matter of interest, if you make the format code:

0.0" liters/d"

rather than

0.0 "liters/d"

Thank you Rory, that is the solution! The correct format, as you said, is

0.0" liters/d"

Thank you, problem solved.

Answer 5

Thank you Andreas. My guess is that the space and quote being in the wrong places created the error. I agree that it is a bug if it causes the file to be unreadable. I will report it now that I know precisely which way causes the error and which does not --- this needs to be fixed.