2

I have read How to set decimal separator in Libre Office Calc? but I haven't found an option for this:

Is it possible to set both . and , as decimal separator in LibreOffice Calc?

In France it's , but as I use both French and other-regions documents, it would be helpful to have both accepted by LibreOffice Calc.

2

You can set the language (and thus, the decimal separator) even per cell (or per document, see below). Thus, if your language is set to French, with , as decimal separator, you can still use . as decimal separator (and accept documents respectively) if they have a proper language setting.

You can check this yourself: just modify the cell's language setting by using the cell formatting features (context menu or Menu Format -> Cells...). Thus, you can even mix different decimal separators in the same document.

Here's a small example:

enter image description here

My localisation is set to German, so the comma is the default decimal separator. A2 and C2 contain both the value 2.5. A2 was formatted with en-us language settings (has to be done before entering the value!), thus the value is recognized as number, and the sum in A3 is calculated correctly. C2 has no special language format, thus no number recognition, wring sum.

EDIT:

To set the language for a whole document, the easiest way is to modify the default cell style. To do so, open Menu View -> Styles or hit F11. Right-click on Default, select Modify, and select the Numbers tab. There you can set the default cell language setting.

After modifying the default cell style, you may have to restore the numeric values, e.g. by doing a regex-based search and replace (search for ^.*$, replace with & - see https://superuser.com/a/394170/84724 )

| improve this answer | |
  • Very interesting! How to set the language for the whole document? (And not per cell; because if LibreOffice has to store lang metadata for 100'000 cells this is duplicated data) – Basj Apr 29 at 14:17
  • @Basj: I've amended my answer to cover that point, too. – tohuwawohu Apr 29 at 14:43
  • Thanks @tohuwawohu. Fun fact: let's say the language is initially French for a .xlsx file. Let's say the numbers are like this: 123.45, and then you perform this (F11 > right click on Default > Modify > Number > English) then ... the cell 123.45 is transformed to '123.45 i.e. text, arghh! So the only solution is to do a Search&Replace . by ,, and do the language change after... But if you have two operations to do in this process (Search&Replace + Lang. change), then the lang. change is not really so useful... – Basj Apr 29 at 15:28
  • 1
    @Basj: i can't test it myself, but you could try to apply the dirty hack described here: superuser.com/a/394170/84724 to get rid of the single quote. If this works in your case, too, you could proceed by (1) adapting the default cell style and (2) transform the "text number values" to number values again... But you still have two operations, so no really nice solution. Would be nice to know how excel handles this use case... – tohuwawohu Apr 29 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.