Ask Your Question
1

csv column weird

asked 2017-12-30 10:52:44 +0200

OldNick gravatar image

updated 2017-12-30 11:00:15 +0200

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.

edit retag flag offensive close merge delete

Comments

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.

robleyd gravatar imagerobleyd ( 2017-12-31 00:04:37 +0200 )edit

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

OldNick gravatar imageOldNick ( 2018-01-01 03:12:34 +0200 )edit

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.

robleyd gravatar imagerobleyd ( 2018-01-01 05:48:33 +0200 )edit

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

OldNick gravatar imageOldNick ( 2018-01-05 13:24:24 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2017-12-30 11:44:05 +0200

robleyd gravatar image

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.

edit flag offensive delete link more

Comments

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 gravatar imageOldNick ( 2018-01-08 05:57:18 +0200 )edit

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

OldNick gravatar imageOldNick ( 2018-01-08 05:58:23 +0200 )edit

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 gravatar imageOldNick ( 2018-01-08 06:25:06 +0200 )edit
0

answered 2017-12-30 20:21:24 +0200

OldNick gravatar image

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

edit flag offensive delete link more

Comments

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.

robleyd gravatar imagerobleyd ( 2017-12-31 00:09:56 +0200 )edit
Login/Signup to Answer

Question Tools

Stats

Asked: 2017-12-30 10:52:44 +0200

Seen: 83 times

Last updated: Dec 30 '17