To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=5658
------- Additional comments from kke...@openoffice.org Wed Jun 3 12:09:44 +0000 2009 ------- I think I may actually have stumbled upon WHY this problem occurs in so many Excel-generated spreadsheets. Turns out that it no amount of training would solve it, because Excel actually makes the change automatically. In some cases, users actually have to go out of their way to NOT enter numbers as text. Actually, there is at least one, and probably two more ways this happens: users may intentionally do it for various reasons. And it probably also happens when you import data from CSV and other formats, although I have not confirmed that. Try this (I tried it in Excel 2007). It is important to do it in exactly this sequence. open a blank spreadsheet. Do not touch the format of cell A1 (it should be formatted as General) Type the number 7 into cell A1 Change the format for cell A2 to "Text" Type the number 6 into cell A2. Make sure you type JUST the digit - no quotes or anything else. Note that the number six will be left-aligned due to the text formatting. Note, but do not change, the formatting of cell A3. It should be "General" Type the formula "=A1+A2" (without quotes) into A3. You will see the number 13, left-aligned. Note the formatting of cell A3 again. You will see that it has changed to "Text" Save the spreadsheet as XLS file. Open the spreadsheet in OpenOffice. Voila. Remember: you haven't entered a single quote in Excel, only digits. Yet just based on the formatting of the cell, Excel treats the 6 that you typed into A2 as a string. Even if you later change the cell format to "General" Excel will keep the number as text! In the real world, this is very likely to happen to parts of a spreadsheet by accident. Imagine a spreadsheet with 10 columns. You format each column appropriately for the data it should hold. Later you insert a new column between two columns that are both formatted as text. How likely is it that you'd realize the new cells are also formatted as text? Maybe you'll realize it an hour later when the formatting is off. But it's too late: once you enter data into a text-formatted cell, the data stays a string even if you later change the format. The second way this probably happens: it may also be an intentional "trick of the trade" of experienced Excel users: "you can save yourself a lot of work formatting the cells by typing in the numbers in the correct format, if you start it with a quote". I came across http://excel.tips.net. Several of the tips listed there deal with this and a few related issues, including a question "If you have a range of numeric values in your worksheet, you may want to change them from numbers to text values. Here's how you can make the switch." (the answer: copy the cells to the clipboard, format the cells as text, paste the data back in - exactly as I outlined above). --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@sc.openoffice.org For additional commands, e-mail: issues-h...@sc.openoffice.org --------------------------------------------------------------------- To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org
