Convert field from text to numeric values in Calc 6

[Albireo]

I have a problem with cell-properties (numeric/text fields.) I don’t know to handle this.

For example.: One CSV-file I want to open with LibreOffice Calc have 25000rows and 20columns. Some columns are text fields and some other is numeric fields. When the file is imported into Calc, not all cells have the same type (text/numeric), in the same column. The biggest problem I have, is if I want to compare these cells with a column with only numbers. When the CSV file is imported with columns with only numbers, the numbers can contain spaces or decimal numbers with a point “.” (in Sweden we have the comma “,” as decimal sign). First, I usually clear the contents of the trash eg.=STÄDA(RENSA(A1))(Swedish LO), and replace the decimal characters eg.BYT:UT(A1;".",",")(Swedish LO), and finally use the instruction eg.TEXTNUM (A1) (Swedish LO) to convert all cells to numbers. So far, there are usually no problems. The problems arise when these numbers (from cells consisting of formulas) are copied to the original cells. Now these cells contain valid numbers, but these cells are formatted as both numbers or text.

My wish is to be able to mark the entire column and choose the format “numbers”. It works if all cells in the entire column has text format, but it doesn’t work if some of the cells are numeric an some is formated as string. It is acceptable that cells that do not contain numbers do not change to numeric. (eg headers). But why doesn’t all other cells change from string to numeric?

What am I doing wrong?

[mikekaganski]

You need to setup your import correctly, instead of trying to fix that later. Or in the case of already imported data, you need to use data conversion process, not formatting data: formatting a cell never changes the data type stored in it.

As you are importing numbers with decimal separator different from used in your locale, you should simply change the locale at import (say, to en_US), or mark specific columns as US English (right-click the column title in the CSV import dialog). The data conversion tool for already existing data is Data→Text to Columns..., which is ~identical to the CSV import dialog.

Also you may use Find&Replace on the imported columns, which would also give you the conversion (see this FAQ).

If you need more specific advice, then provide a sample data file.

[Albireo]

Thanks for the tip “…or mark specific columns as US English (right-click the column title in the CSV import dialog)…” it works!
The CSV files that I usually import to Calc usually have clearly defined fields. One CSV file has nearly 80 columns with 35,000 lines, another 20 columns and 25,000 lines. Had hoped that fields in the CSV file surrounded by quotation marks ("") should be autiomatically interpreted as text. A little hard to pick each time the fields that must not be interpreted as standard. My desire is to be able to simplify the import. Should look a little closer at the import function in Calc.

In the example, I only mentioned data from a CSV file, but often I get a Calc sheet of cells, copied from Excel, information from the Internet and so on. This sheet may contain “anything”. It is these sheets that I want to handle so text fields become text fields (without controll characters etc.) and numeric fields become numeric, in order to be able to search / compare and manage later without missing the result.

[mikekaganski]

but often I get a Calc sheet of cells, copied from Excel, information from the Internet and so on … It is these sheets that I want to handle so text fields become text fields

Well - citing myself:

The data conversion tool for already existing data is Data→Text to Columns…, which is ~identical to the CSV import dialog.

[SM_Riga]

Had hoped that fields in the CSV file
surrounded by quotation marks ("")
should be autiomatically interpreted
as text.

Please check when importing CSV if Format quoted field as text option is checked. Then Calc will interpret such field as text, regarding of contents.