[Solved] Numbers not recognized as numeric

[webmanoffesto]

I was working on a spreadsheet I use for bookkeeping. I download transactions from a bank, in a text or CSV format. Then I either open that file in Libre Calc or I paste the transactions into Libre Calc. Suddenly yesterday I was doing that and all the numbers were not recognized as numbers. Sometimes the first number in a series was a number, and from the second instance on it was not.

I used =isnumber and it said False
I use =value to try to extract the value
I confirmed that it didn't begin with a single quote, didn't have extra spaces before or after, etc.
I tried regex clean: e.g. =VALUE(SUBSTITUTE(CLEAN(A1), CHAR(160), ""))

Nothing worked.

I usually find Libre Calc a very solid and stable application.

Can you tell me what caused these problems and how to resolve them.

The only solution I found was to use Google Sheets. I guess that has some automatic data cleanup. That worked. But I prefer to go back to Libre Calc.

[FJCC]

Without an example of the data, it is very hard to make any suggestions. I expect you don't want to post your bank information, but you can modify it easily. Open the csv file in a plain text editor, delete everything except the first few lines, and change the content of those lines so the data are no longer confidential. You should then confirm that the edited file still has the problem when loaded into Calc. Post the file here so people can duplicate your problem and search for a cause. It's possible the forum won't accept a csv file. If so, please post the file on a publicly accessible site.
To upload a file, click Post Reply and look for the Attachments tab just below the box where you type a response.

[robleyd]

When you are presented with an ASCII a text import window when opening or pasting data, make sure you check the option Detect special numbers.

See also How to convert number text to numeric data

[karolus]

…with an ASCII import window …

[nitpicking]there also quite a lot encodings beyond ASCII[/nitpicking]

[webmanoffesto]

Today I made sure to download the transactions in .txt format. My spreadsheet is again working the way it should.

I'm not sure that I used .csv format yesterday, but if I did, that could have been the source of the problems.