Automatically change dot to comma as decimal notation

I use Calc for work. Some numbers I insert from some documents or other software use dots as decimal separator. However in my language we use comma as a decimal separator.

Is is possible to configure so that Calc recognizes both symbols as decimal separators, and preferrably also switches the dots into commas when used as decimal separators?

Every time I’m forced to manually adjust the imported values. It’s a big waste of time, and I can’t change to a language that uses dots as decimal separators, because those are the exception here and not the rule (the majority but not all documents and programs use commas). This mixup of differing standards is a real annoyance, and having a way to deal with this permanently would be nice.

For example, you could use the Edit>Search and Replace menu to replace all Commas in Dots at once.
The values are then also displayed as numbers.

In addition:

Not as a general rule, because only you can tell calc if 1,900 copied from the web is american 1,900.00 or german 1,9 with 3 digits after the separator.

And considering numbers like american 1,900.5 wich is 1.900,5 in Germany you can’t use a simple “exchange” by two runs search and replace, because you’d create malformed 1.900.5
It is possible to remove the separators at thousand and replace the remaining separators.

If you deal with csv-data I’d suggest to generate a set of macros, wich imports the data, but for “copied from the net” there is no standard.

But: If you imported all “texts with figures” to real “numbers” and keep this inside odc/xls/clsx-files the shown separator is not important for calculation, as internally you’d find somthing like 019 E04 for 1900.00 if using floats.

You could copy-paste the external text while using LibreOffice in a locale (e.g. en-US or en-GB or en-DK) that uses the dot decimal separator, even as a second LibreOffice instance with a different user profile. Or you could pre-format a range of cells with a number format of a locale that uses the dot decimal separator before pasting the copied text and then apply a format of your locale (or just hit Ctrl+M if it’s just numbers). Or you fix the pasted text afterwards. Or use the CT2N extension. See this FAQ.

1 Like

Or es-MX: Español (México).

Note also that Paste Special dialog (Ctrl+Shift+V) for external data has a Use text import dialog even for text that doesn’t normally show such dialog (e.g., one-line text), and you can define the import settings (e.g., locate of the data) there.