Handle pasting of decimal with dot (1.7) when locale is expecting comma (1,7)

Question

I've a datasource where the dot is used as decimal separator, i.e. : 1.49 means "one and almost-half". Cannot change it.

This number must be copy-pasted to a numeric cell in Excel or Calc. The file is public and anyone can get it (get it here) so I don't have control on the user PC/program.

Most of the users are italian with an it_IT locale, where decimals are comma-separated, i.e. : 1,49 means "one and almost-half" (note the comma instead of the dot).

When the italian-locale user tries to copy-paste 1.49 from the datasource into his/her numeric cell, Calc/Excel goes banana because it doesn't recognize the number as a decimal.

If I set the language on the cell, everything works.... until I close the program. If I open the document again, the setting is lost and it doesn't work anymore

So, my question:

1. Is it possible to force the locale of the file/sheet to "en_US", no matter the user-defined locale?
2. Is it possible to make the Language attribute stick?
3. Any other idea? (no "ask your user to change the setting", please)

I'd rather avoid macros, if possible.

Thanks!

Update: I'm on Windows 10 x64 and I just upgraded to LibreOffice 5.4.4 x64. The per-cell language setting still doesn't get saved. It's not even clear to me if it's supposed to be saved or if it's just a view setting (would guess the former, since the save button lights up when I do it..)

Solution

My friends over at TurboLab.it proposed the best solution so far:

1. set the cell designed to accept the user input as "text"
2. in a temporary cell nearby, replace the dot with comma with something like =SUBSTITUTE(E16;".";",")
3. do the math on the replaced value
4. re-convert it if needed

The final file, for those interested, is available here.

The only major downside is that this only works if the user has a locale where "comma" is the decimal separator, such as italian.

HTH.

Comments

[RonJohn]

Interesting. I confirmed with Calc 5.3.6.1 on Linux that it forgets the cell language.

[wlod]

You used xls format. Excel does not have a 'Language' property for a cell so when you save it in xls it drops this information. ods native Calc format has this property so you can have two cells with different language settings say: 1,49 and 1.49 and still the result will be 2.98 or 2,98 depending on the cell format which contains SUM function. In ods copy-paste cell, close-open file does not harm cell settings. Should you use ods format? Probably not as Excel users will get dirty prompt that the file content is unreadable. So it's certainly interesting if there is any 'easy' solution to this.