8

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

enter image description here

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

2
  • 1
    Interesting. I confirmed with Calc 5.3.6.1 on Linux that it forgets the cell language.
    – RonJohn
    Jan 3, 2018 at 0:48
  • 1
    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.
    – wlod
    Jan 3, 2018 at 14:34

2 Answers 2

2

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.

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .