----- Original Message ----- From: Royce & Faye Green Sent: Tuesday, June 27, 2006 9:59 PM Subject: NUMBER FORMAT IN DOWNLOADED SPREADSHEETS
My apologies; I 'sent' this message prematurely by mistake a little earlier, while trying to operate with too many windows open. What I am trying to say is that the problem occurs when downloading a supposedly spreadsheet table regularly from a proprietary website. The contents of every cell appear in OpenOffice Calc with a leading ' quote mark which identifies them as text, not numbers, even though it is clear that they were all intended originally to represent numeric values: quantities, $ values, ratios, percentages, averages and dates. They look O.K. on the screen & when printed, but (as they are in text format) neither Calc nor MS Excel will conduct any math operations on the values, which I need for comparative purposes, or on a limited extract of some of the numbers out of 'the big picture' for my own limited interest or requirements. This now seems to have emerged as a large & quite widespread problem. I first raised it when I joined this mailgroup 11 days ago (16 June 2006). I received an immediate fix from Johnny by way of a macro he wrote for me to remove the leading ' quote mark; this method was also suggested by Donald Locker. A growing number of other users are now raising the same problem in various other situations; notably Ben Wong last Friday. As I said in my earlier 'Send', I also will admit that my geekspeak vocabulary is virtually non-existent. I find that most of the support pages I have turned to use a language that is totally inaccessible to me. I also concur that OpenOffice help files are very technical & do not give me many clues or help with my simple-minded problems. (I acknowledge that this is a FREE office suite run by volunteers & we should not be demanding too much Help !) I really believe this spreadsheet thing to be a major issue, but when I endeavoured to go into peschtra.com >tips_issue to raise this as an ISSUE (as suggested to Ben today by Peter Kupfer) I could not even get into that page. ( Maybe I am not yet properly registered, but I did not receive the promised return email registering me !) I had been having some on line discussions with Johnny about the spreadsheet cell format problem & thought at that stage, that the ' quote mark > text format situation existed only in OpenOffice & not in MS Excel spreadsheets as I had not 'seen' it there, but as we mutually reviewed things I decided that I would download the data from my provider in the ancient Excel format on my old computer. When I did this today, Lo and Behold ! while it looked the same in Excel but without the nasty ' quote text marks which feature in OpenOffice, MS Excel also will not calculate on any of the cells (because it does not recognise them as numeric values, even though they look like it & do not show the text prefix). What's more, you can not simply re-format the problem cells into your preferred numeric format thru the Format menu or dialog box (as suggested by another user) because Calc simply ignores that instruction (even though its Paste & Format capabilities are much nicer than MS Excel). The rather cumbersome alternative I had adopted previously (based on Dave's question of whether it was an HTML webpage file I was downloading) was to save the downloaded spreadsheet in OpenOffice in HMTL format, re-open it in OO Writer, then Edit> select all, and COPY the document contents into a blank Calc spreadsheet where I 'Paste Special' them as RTF (rich text format). MS Excel does not have this RTF nicety either. OpenOffice Calc then accepts these cells as numbers (without any format prefix) and allows me to operate on them with SUM or any other math functions & to individually re-define their number format. This solution does not work if I open the HTML downloaded file within OO Calc, as Calc does not provide the RTF format as an option in this situation. My solution appears very cumbersome, but at least I can then work on my down-loaded data in OpenOffice Calc. So, that's my long-winded gripe & contribution for this evening; it's time for bed. Kind regards, Royce Green 28 Durran Street TUGUN QLD 4224 AUSTRALIA
