Ask Your Question

user-defined format for numbers being lost.

asked 2015-05-31 12:55:58 +0200

TuftyDave gravatar image

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?

edit retag flag offensive close merge delete


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

m.a.riosv gravatar imagem.a.riosv ( 2015-05-31 13:09:22 +0200 )edit

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.

TuftyDave gravatar imageTuftyDave ( 2015-05-31 14:02:49 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2015-05-31 13:14:05 +0200

Lupp gravatar image

updated 2015-05-31 14:08:46 +0200

... 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

edit flag offensive delete link more


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.

TuftyDave gravatar imageTuftyDave ( 2015-05-31 13:23:34 +0200 )edit

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

TuftyDave gravatar imageTuftyDave ( 2015-05-31 13:24:40 +0200 )edit

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

Lupp gravatar imageLupp ( 2015-05-31 13:58:40 +0200 )edit

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!

TuftyDave gravatar imageTuftyDave ( 2015-05-31 14:14:40 +0200 )edit

"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.

Lupp gravatar imageLupp ( 2015-05-31 14:24:57 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2015-05-31 14:37:41 +0200 )edit

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

Lupp gravatar imageLupp ( 2015-05-31 14:42:23 +0200 )edit

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!

TuftyDave gravatar imageTuftyDave ( 2015-05-31 15:06:04 +0200 )edit

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

Lupp gravatar imageLupp ( 2015-05-31 15:50:50 +0200 )edit

Sorry @Lupp I didn't find neither.

m.a.riosv gravatar imagem.a.riosv ( 2015-05-31 23:34:24 +0200 )edit

answered 2015-05-31 13:16:23 +0200

TuftyDave gravatar image

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)

edit flag offensive delete link more

answered 2015-05-31 13:20:35 +0200

ericm gravatar image

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 Eric

edit flag offensive delete link more


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?

TuftyDave gravatar imageTuftyDave ( 2015-05-31 13:43:34 +0200 )edit

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...

TuftyDave gravatar imageTuftyDave ( 2015-05-31 13:49:14 +0200 )edit

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.

ericm gravatar imageericm ( 2015-05-31 15:53:17 +0200 )edit

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)

ericm gravatar imageericm ( 2015-05-31 16:01:13 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-05-31 12:55:58 +0200

Seen: 745 times

Last updated: May 31 '15