Csv column weird

[OldNick]

I imported a csv delimitered file to a spreadsheet and there was a column of $values…$400.00…$350.00 etc.

On the above a cells formula of …= (A1+A2) will produce 750.00 (no $)

But if I try SUM or COUNT etc on a column of such derived numerical-only cells, it fails. They all produce zero …0.

If I do COUNTA, it will give me a figure (surely a COUNT is a COUNT of cells?)

But sum(A5:A12) = 0

I tried formatting the column to a simple numerical format, but it stayed unchanged…formatted as currency (???), and still no ability to SUM or COUNT, but still with the ability to have MATH operators on individual cells.

If I take a cell and ask for “=a5”, I get A5’s value…but if I try a Sum on a column of such results…0.

However if I enter the formula “= A52" I get a value of twice A5. So I enter "=A52/2” and it gives me a value of the value of A5.

…and then if I copy and paste that formula to a whole column, I can SUM(G5:G12) and it works…as does COUNT.

I am puzzled and would greatly appreciate help, please. All I wanted was to sum a column of imported, apparently numerical, figures. Even now I have a workaround it will be a nuisance.

Nick

EDIT: Further to this, if I actually enter -400.00 into a range of cells that are in my imported column, then format those cells as Numeric 1000.00, I still cannot SUM them. I noticed that at the beginning of each cell’s text there was a single quote. Removing that manually from each cell made SUM work.

Asking for such ideas a RIGHT (cell number) X digits still did not let SUM etc work.

[robleyd]

Your Edit comment suggests the cells are formatted as Text. You can confirm this with View | Value Highlighting (Ctrl+F8). Text cells are formatted in black, formulae in green, and number cells in blue, no matter how their display is formatted.

[OldNick]

OK. Thanks yes you are right. They are text.

But I am confused:

• if I Format Cells and ask for them to be numeric, why does that not work?
• if they are text, how come I can do such maths as A2+A3 and get a numeric result?

Nick

[robleyd]

Changing the format of a cell doesn’t change the contents; if the content is text, it will remain text no matter how you format the cell.

[OldNick]

So my question remains. If it is still text, then how come maths on that cell will work (A3 = 3, as in 2X A3 = 6 in another c) but SUM and COUNT will not. How come if I create a columnF that is based on F=A2*2/2, then copy and paste that formula to F3:F20, the column becomes numeric and can be SUMmed and COUNTed, because I did a MATH calc on a TEXT cell?

Not just being combative. It seems like the TEXT cell is totally different from the column it’s in. Maths on cell = OK. Stats on Column = not

[robleyd]

When you import the CSV file, under Other Options check Special Numbers.

If you can’t re-import, use Data | Text to Columns to convert from Text to a suitable number format.

If this answer helped you, please accept it by clicking the check mark to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.

[OldNick]

Thanks again for your reply. I can’t see how to attach a csv sample.

BUT

After playing about a bit, I have found that I had to UNtick “Quoted Field as Text” when importing the csv file. That, with Detect Special Numbers, allowed all the Sum and Count functions.

I am not sure what the default was, but if the Quoted Field as Text is default, will that not cause this problem all the time, as a csv file is quite likely to have Quotes for each field.

[OldNick]

I still do not get how come Libre can do A1+A2 = , or A2*2 = to text fields, but cannot sum.

[OldNick]

Sorry. Also, if I select the column then do Text to Columns, the Quoted Fields as Text is greyed as is Detect Special Numbers. Quoted Fields as Text is not ticked, but the action does not make the column a number field column

[OldNick]

Thanks for the reply, but neither of those options works for me. Re-importing with Detect Special Numbers did not make any difference and Data/Text to Columns I saw no option to select a number format. Detect Special Numbers was greyed out. Whatever I tried still produced 0 as a SUM

[robleyd]

Select the range of numbers you want to convert to numeric; Data | Text to Columns - click OK. See what the result is.

If this still doesn’t achieve the result you need, perhaps you could attach a sample csv and/or ods file that shows the problem. I’ve given you points to do so.