CSV imported negative numbers as text, "text to columns" does not work

I have found two previous answers which both say to use the “text to columns” entry in the data menu, but it does not work for me.

I have a .csv file which I opened and it looked fine at first so I began working in it.

However, now I discover that whenever a cell has a negative number, there’s a small “’” sign in the beginning of it which apparently isn’t really a character but an indication that the cell is text when it should be a number.

The problem seems to be that the cell is otherwise correctly formatted, so the only way I can get “text to columns” to do anything is to force it to find a delimiter and split the cell into two, but I don’t want that. The cell contents is correct, I just want it to understand that the “-” sign is a negative sign and not a dash and I see no option for that.

Do you say, that using Text to Columns on that document, the texts with negatives don’t convert into numbers? Please provide a sample ODS.

I can’t even manually delete that bloody “’” sign…

numbers.ods (12.7 KB)

Interesting. This looks like a regression; in v.7.0 it worked as expected…
Ah no. If I use a locale where comma is decimal separator, it works just fine. But in a locale with decimal tot, it can’t detect a string like “-123,45” as a number. It expects dot.

1 Like

Character set and locale are greyed out for me. Could you please expand on how you managed to get it to work?

EDIT: OH! Just doing a regular find and replace substituting “,” for “.” made everything a number. Thank you!