Number format comma lost when saving spreadsheet

Question

Hi

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

Answer 1

Sorry, I don't have a solution for you despite my best fiddling but I'm throwing your reply a +1 for thinking outside the box. I'm a big proponent of using the available resources to the fullest extent and your number manipulation within a custom number format was impressive.

I found that the comma does not appear to be lost during the save, reopen process. Rather it is lost immediately when Excel stores the custom number format. You are able to achieve and retain the displayed 'conversion' because that seems to be determined before the number format is stored. The only option I see to retain it through multiple sessions would be to rewrite it in a Workbook_Open macro.

Again, well done despite the limitation(s).

Answer 2

Hi Arshad

Thanks for the quick reply

I have tried in both .xlsx and .xls formats and get the same result.

The following link should give you an example spreadsheet.

https://dl.dropboxusercontent.com/u/48629996/Book4.xlsx

In the spreadsheet example I have formatted the Cell C2 as #,##0,"mg/L"<cr>%%% but the saved version loses the comma before "mg/L"

If you edit the number format and re-insert the comma you should see the displayed value change to 54mg/L

If you then save the sheet and re-open it the comma will again disappear

Note:  The <cr> has been inserted into this number format using <ctrl-J> which puts the %%% on the next line which isn't visible on the spreadsheet because the row height is fixed to only show one row height.

Answer 3

What is the format of the file, .xlsx or .xls?

I am not able to repro this with my excel 2010.

Can you upload a spreadsheet?