Comma lost in number format when saving the sheet

[kumar_777]

Hello All,

A bit of background...

I thought I had found a way to show a gram per litre (g/L) concentration in mg/L.

I gave the value a number format of "#,###0,"mg/L"<cr>%%%" (Using <ctrl-J> to enter the carriage return) and turned on word wrap to hide the %%% on the next line.

This works because the comma before the "mg/L" divides the displayed number by 1000 and each of the % multiplies the displayed number by 100.

So Number/1000*100*100*100 = Number*1000

It works fine with a number 0.05431 being shown as 54mg/L

Now the problem...

When I save the spreadsheet with this number format the comma before "mg/L" in the custom number format disappears when the spreadsheet is reopened. That is, the number format changes to "#,###0"mg/L"<cr>%%%" grrrr

I have tried lots of variations of the number format whilst keeping the basic premise of using comma's and percent symbols to make it show mg/L but it seems that the comma always disappears.

Does anyone know why the comma disappears when the spreadsheet is saved and how to stop it being lost?

Thanks in advance

 

[mole999]

I think you have the answer, the comma is part of the formatting in numbers, so is not a retained visual. the only way i think would to be have the number saved as a text string, and then it won't operate numerically