How to replace commas with dots in libreoffice calc as a decimal delimiter (closed)

mlij · October 31, 2019, 6:24pm

I live in the US and I try to replace commas with dots as a decimal delimiter in the spreadsheet which came from Spain
using LibreOffice 6.3.2. I was able to replace commas with dots in the numbers which have 0 as a single-digit e.g
0,9444 was replaced with 0.9444 using Find and Replace. But this does not work with numbers like 10,176.
Below is a part of the spreadsheet after Find and Replace.

0.8984
0.9086
0.9228
0.9407
0.9508
0.977
0.9874
0.9972
10,176
10,343
10,479

I appreciate any advice. Michal

mikekaganski · October 31, 2019, 6:41pm

What is the format of those spreadsheets? Is that ODS or maybe CSV? In the latter case, the proper way is just select correct language/locale in the import dialog.

Villeroy · April 10, 2022, 3:45pm

This looks as if the figures are already meant to be English with the decimal point in 0.9972 and comma as thousands separator in 10,716. I can only guess that the first one is meant to be a decimal below 1 and the second one is meant to be integer 10716. A software must not guess this.
Text or number? This is the question since 30 years of improper use of MS Excel.
It is very easy to do all kinds of import or conversion with Calc. It is also very easy to mess it up completely when you have no clue what type of data your are dealing with.

erAck · April 13, 2022, 12:18am

Fwiw, this is just another case of the FAQ.

mlij · October 31, 2019, 6:53pm

The original data came as xls file. I copy and paste these data into ods file.

mlij · October 31, 2019, 7:09pm

I saved the ods file as csv, opened it with jedit, and replaced remaining commas with dots.

mlij · October 31, 2019, 7:29pm

After uploading the edited the csv file into Calc with commas replaced by dots commas appeared again.

erAck · November 1, 2019, 3:13pm

Make sure it’s not just a display format that displays numbers using a locale that has the comma decimal separator, e.g. Spanish (as you mentioned that). On such cell look at menu Format → Cells… (or Ctrl+1), tab Numbers, watch the Format and which Language is applied.

And please don’t use Answer as a solution if it isn’t a solution to the original question. Either use add a comment or edit your question to provide more details. Thanks.

ChristianBueno · September 3, 2020, 4:22pm

Libreoffice version 6.4.6.2

OS: Linux Fedora 32 Workstation

If you copied & pasted a column or some columns with numbers that have comma there from another file, the best option I found was the next.

Select your columns
Go to the menu and click Edit> Find & Replace
Type in Find: , and in Replace: .
In Other Options check Current selection only
Then Replace All and Close.

libredyr · April 10, 2022, 3:48am

I use:
=SUBSTITUTE(SUBSTITUTE(C1;",";"");".";",")
where C1 is the cell.

erAck · April 13, 2022, 12:17am

That only creates another text string but does not convert to a numeric value. (and does the opposite of what the original questioner wanted, your’s replaces , comma group separators with empty string and . dot decimal separator to , comma decimal separator).