Hi Bill, I had a similar problem with CSV i.e. with the apostrophie turning the numbers to text. I simply downloaded the file again and it came in correctly without the apostrophie and the numbers behaved as numbers, not text. I had never had this pronlem before and I don't know what, if anything, I did differently.
Regards, Alan. Sent from Samsung tablet -------- Original message -------- From: Bill Dillinger <cjb...@his.com> Date:15/05/2017 15:21 (GMT+00:00) To: mar...@groenescheij.com,users@openoffice.apache.org,Brian Barker <b.m.bar...@btinternet.com> Subject: Re: Trouble with Calc Hello, I have received helpful suggestions from Brian and Martin and very much appreciate them. Thanks for the help and any additional notes that may yet come in. I realize now my original message was not completely clear because I did not fully understand my problem. As was suggested by both my problem was due because the numbers in the .csv file were being brought in as text not numbers. One suggestion was to click the "Detect special numbers" option when opening the file. I did try this and it didn't work. It was suggested to use the function =VALUE(c2) in a new column in Calc to convert the text to numbers. When I did this I got Err 502. Then Brian suggested the problem could be due to leading blanks in the field. This seems to be the real problem. If I remove the leading blanks in Calc the text becomes numbers. Also if I remove the leading blanks in the .csv file the fields will be input as numbers. I would be impractical to remove leading blanks manually through out the file. I know editor questions are not appropriate to this forum but FYI I will mention what I have learned so far. Using OpenOffice Writer I see that the fields start with a tab character. I am working on it but have not found a way to find and replace the tab character in any of the editors I have access to on my computer. Still working on it. Thanks for the help. Bill Dillinger On 5/15/2017 8:46 AM, Martin Groenescheij wrote: > > > On 14/05/17 5:22 AM, Bill Dillinger wrote: >> Hello, >> I can't seem to manage Calc with even a simple problem. I am trying >> to read and work with a spreadsheet made from a .csv file with >> columns of numbers. I really have tried to read and work with the >> HELP file. >> >> First I could not add 4 numbers but with lots of tries found that it >> seemed to be because all my numbers ended in .0 and when I did a >> global replace of .0 with nothing I could then add numbers. Once I >> did that I was able to compute an average and then copy the formula >> to other places in the spreadsheet. I first did try formatting the >> number field but that didn't seem to help. If someone could tell me >> why I can't use numbers ending in .0 I would be interested. > > First thing you should check is to see if the number is really a > number. Sometimes when you open a CSV file the numbers > are imported as text. It's good practise to tick the box "Detect > special numbers" when you open or import CSV files. > It's easy to check if a number is imported as text by selecting a cell > look at the top bar if the number is preceded with ` e.g. > `123 it is a text field. Changing the format doesn't help. > You could do two things: > 1 deleting all ` in the cells which doesn't make sense if you have a > lot of numbers to change > 2 start from scratch and check the "Detect special numbers" box > >> >> Second my current problem is I cannot find the spread of 4 numbers. I >> tried =MAX(C2:C3;C4;C5)-MIN(C2;C3;C4:C5) and it worked! Then when I >> copied that formula into a new location to get >> =MAX(C6;C7;C8;C9)-MIN(C6;C7;C8;C9) it gives me the incorrect value 0. >> If I copy or type the formula into any other cell it will not work. >> >> If anyone knows what my problem is I would appreciate it. >> >> Thank you >> >> Bill Dillinger >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org >> For additional commands, e-mail: users-h...@openoffice.apache.org >> >> > > --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
