User-defined format for numbers being lost

In a spreadsheet I added a user-defined format for numbers. Libreoffice forgets this every time I close the sheet so I have to re-create the format mask every time. Is there anything I can do to get around this?

Please what is the format that disappear?, LibreOffice version? Operating system? Type file used to save?

The format mask is: 0####" “###” "###
My Libre Office Version is: Version: Build ID: 420m0(Build:2)
My OS is Linux Mint 17 Cinnamon 64-Bit
File created opened and saved as default ‘.ods’ file.

… I added a user-defined format for numbers. …

How did you do this exactly? Did you create a cell style containing a user defined format code in the proper place on the ‘Numbers’ card. Was it done by direct formatting?

Would you mind to post an exact copy of this code ( using the preformatted text tool of this forum’s editor ) as an amendment to your question? I cannot remember details, but there are codes that may work in an open sheet but may get mangled when written to the file. ODF might not support a variant that Calc will accept.


The additional remarks by the questioner strengthen my suspicion. There are user defined number formats which are interpreted by Calc as the user expected but cannot be saved and reloaded in exactly the same way. I tried but could not yet find in what way ODF specifies the file format with respect to user defined number formats. But obviously what users can weave cannot in every case persist. I also cannot find any assurance that “digits on demand” (#) can legally be used the way you did.

You may try the “surrogate” number format code "0"#" "###" "##0

I select a cell (or range of cells) containing numbers.
I click on ‘Format’, then ‘Cells’ then look under the ‘Category’ in the window that opens.
There you see User-defined’ with just one entry ‘-1 234’ and it’s mask is ‘#" “###” "##0
I add my new mask ‘0####" “###” "###’ and click the green ‘Add’ button and the ‘Edit’ and give it a new name.
All this works perfectly and the mask does exactly what I want it to do.
The mask stays until I close the sheet, and is gone when I re-open it.

There isn’t a problem with the mask or how it works, It works perfectly, the system just refuses to remember it!

This is what I suspected.I will append something to my above answer.

Thank you so much Lupp.
I’m not at all sure what “surrogate” number format code “0”#" “###” "##0 is or how it works, but it solved the problem!

“Surrogate” is simply a word I chose trying to expain my thoughts. I found this specific code playing with your code and the messed up remnants after save/reload. Please don’t feel sure that any Calc version of the future will interpret this format code in exacly the same way. Using unspecified means is always kind of hazardous.

Seems that odf doesn’t accept the # placeholder between left zero and decimal separator, in any case no difference replacing # with zeros, they don’t suppress intermediate zeros if it was the purpose. I think the natural way to do it it’s all zeros. 000" "000" "000

@mariosv ACK. Did you find any specification for number format codes (how to save them) in the ODF documents? If so, let me know, please.

I’d like to reward Lupp with some votes, but I just joined this forum and for the life of me I can’t figure out how all this voting, points, badges, stuff works!
It tells me I’ve got 30 votes to use, but it doesn’t allow me to use any because I need 5 points to vote up someone!?!?!?!?!?!
Seems more complicated than the Libreoffice question I came here with!

Don’t worry! I also don’t know that system in detail.

Sorry @Lupp I didn’t find neither.

When initially looking, there is one mask for ‘user-defined’ format in calc.
I am clicking the little arrow to add a new one and editing the name so it has a new name.
While the sheet remain open, I can call up that mask and use it.
But EVEN IF I SAVE IT, the next time I open it, there is only the one original user-defined number mask format.
Does everyone else have the same problem or is it just me?

(Sorry I can see someone has asked me questions as I was typing this, I’ll post anyway and answer the questions later)

This should work.
Open a blank sheet.
Set up your custom formats on the sheet.
Create a cell style from the cell(s) containing your custom number format (or formats)
Save your sheet as a template.

In template manager, set the template with all your custom formats as the default sheet.
Now, when you open a new sheet, all your custom formats will already be defined.

Hope that helps

Nope… sorry Eric can’t get that to work either…
However I think there’s a step I’m missing…
You say “Create a cell style”
I don’t know what you mean by that or how to do it?

I created an empty sheet and created my new user-defined mask, and formatted a range of cells.
Saved the sheet as a template. I clicked the button to say it is the default.
Started Libreoffice again, but just to be sure I went to and opened the template I’d just saved.
Again, exactly as before my new User-defined mask no longer exists…

To create the cell style you need to use the style & format menu - you can access it in the sidebar - button under properties)

Once you’ve created you custom format and clicked on the green tick. Make sure you’ve got the cell selected and open the style & format window (s&F).
Top right of the s&f window you’ll find a button to create new style from selected cell.
click on the button to create the style - you’ll be asked to give it a name, then click ok.
Your new style will appear in the list.

Once you’ve created the style save as template and set as default. The style will be retained and can be used in any sheet built from the template. (You’ll see your format mask in the numbers format list on the cell dialog)