Convert columns to numbers

mortoray · December 7, 2020, 1:45pm

I have CSV files with various formats of numbers and do not know how to get Calc to recognize them as numbers.

The “Standard” column type does not recognize them.
The “Data|Text to Columns” function does not recognize them.
I’ve tried converting commas to dots and reversed, and it does not recognize them.

The format the file has is a comma-as-decimal, such as:

12,18
-9,99

In other files I have numbers like:

12.18
9.99

Additioanally, trying to write a formula in the loaded file, even after saved as ODS, does not work. Entering someting like =H2 simply displays =H2. It works in new sheets.

gabix · December 7, 2020, 1:48pm

Try US English for the column type when importing the file.

mortoray · December 7, 2020, 1:55pm

Sorry, I forgot to add that I have both number formats. Changing the Language does not appear to help – testing the comma case now. For example, “Germany” doesn’t import 9,99 correctly, though that is the German format.

gabix · December 7, 2020, 2:04pm

Do you mean both formats in the same column? There is no option for such a situation. You have to pre-process or post-process the file somehow.

Lupp · December 7, 2020, 2:48pm

Additioanally, trying to write a formula in the loaded file, even after saved as ODS, does not work. Entering someting like =H2 simply displays =H2. It works in new sheets.

That occurs to be a completely different issue. Either you have wrongly enabled the option >Tools>Options>LibreOffice Calc>View>>Display>>Formulas or you entered the formulas into cells set to not recognise formulas by entering the NunberFormat code @ (‘Text’).

Now the chance this issue might be related to the other one: HOW did you get the csv contents into your sheet? Please be precise about what steps you actually performed. You obviously aren’t experienced enough to judge yourself what might be important and what not.

mortoray · December 7, 2020, 6:10pm

I determined the cause of numbers not being imported correctly in my case. It’s a combination of settings in the import screen.

For the comma-based format I needed to choose:

Language: German
Separated By: Comma, Semicolon
String Delimiter: "
[ ] Format quoted field as text (Not selected)

For the American one I needed to use an English format. And then combinations of those above settings seem to make the difference. The “Format quoted field as text” appears to be the most significant.

Post-import I still have no idea how I’d reparse a column. The “Data|Text to Columns” does not have all the options available.