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:
- Is it possible to force the locale of the file/sheet to "en_US", no matter the user-defined locale?
- Is it possible to make the Language attribute stick?
- 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..)
Best Answer
Manual solution, to be repeated every time you paste:
After the copy & paste; the pasted cells are selected...
Immediately use Find & Replace, first replace any "." (thousands separator) with "" (i.e. "nothing"), then replace "," (original decimals separator) with "." the new decimals separator).
You might need to use "Paste special" to add 0 or multiply by 1 to get these to be interpreted as numbers (==> place 1 or 0 in any cell, copy it, then do "Paste special" and select to "add value" or "multiply value").
Automation: "Record a macro", do the above, "stop recording". In there you also have the possibility to assign a shortcut key.