On Mon, 15 May 2017 10:21:21 -0400 Bill Dillinger <cjb...@his.com> wrote:
> 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. In OpenOffice Writer use /Edit /Find and Replace. In the Find window enter \t In the Replace window enter whatever you wish to replace it with (in this case, leave it blank). Drop down More Options and select Regular expressions, then Replace All. RoryOF > 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 > > -- Rory O'Farrell <ofarr...@iol.ie> --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org For additional commands, e-mail: users-h...@openoffice.apache.org
