Eureka! This proves, once again, that four heads are better than one. Yes, I've imported data from different applications for the same length of time that I've worked with them. Therein lies the problem. My client created an Excel export from an antique database program, without knowing anything about the nuances of one Excel version over another. That's why I requested him to perform his export to a .csv file. His software may have been able to do that, but he'd never done it and didn't have a clue why I would make such a request. So, I relented and said, send the Excel file. It was a real mess with headers exported on every page because he either didn't understand the request or his software was incapable of exporting without headers. As Gene suggested, I did think of the apostrophe, which is so visible in Excel files. However, my inspection revealed no apostrophes in any cell. I did find, however, a leading space. Ah, this must be the way the exported database file indicates text. As a test, I clicked on a suspect cell, then right clicked for cell format. Sure enough, on the number format tab, the cell was listed as text. I then changed it to number with two decimal points and a thousands separator. In the empty column to the right, I then hit sum and included the changed cell with the number formatted cell above it. The sum equaled only the number formatted cell. My second try changed the format to currency, with the same result. With 4499 lines to go, frustration was setting in fast. Before beginning the ineffective trudge through 4500 lines, I left it alone for a few minutes. Without knowing which version of Excel I was working with, and the ineffective attempts experienced, I clicked on the VIEW menu and ticked Value Highlighting, as suggested. There, in living color, the correct cells were blue, the cells containing formulas were green, and the text cells were black. Now, what to do about 4500 entries? I quickly remembered a solution I've used for years in similar cases. I defined the suspect column as a range, which I then copied to Windows Notepad. I knew that Notepad (or any other plain text editor) would strip everything attached to the numbers. I then SELECTED ALL, and pasted it into my spreadsheet. All of my black text numbers were blue. I checked my checked columns and everything balanced to the penny. 4500 lines were changed to the correct format in a few seconds! The frustration arose because I was unable to correct the number formatting without retyping, as Joe said, which was not an option for 4500 entries. I also consulted the OpenOffice manual by Greg Perry. While his book is several years old and pertains to earlier OO editions, this VIEW menu option was available then. He also had the sage advice of entering text as numbers with a leading apostrophe, just like in Excel, for ZIP codes or stock numbers that you want to use as text. He does mention that the apostrophe is never visible, but prefixes a leading space. He offers no solution for changing a number as text to a number as a number, without retyping. Logic tells me that I should be able to click on a number formatted as a text cell and and change the format to a number with whatever attributes I choose; but, it doesn't work. Have I missed something? Or, should I report this as a bug? It's been awhile since I've used Excel, but it seems that I remember that you can change a text formatted number to a number with the formatting option. Thanks to all who contributed their insight, I sincerely appreciate it.
Suzanne L. Perry --- On Fri, 3/20/09, Joe Smith <[email protected]> wrote: From: Joe Smith <[email protected]> Subject: [users] Re: Excel File Import into Calc To: [email protected] Date: Friday, March 20, 2009, 9:00 PM Gene Young wrote: > Joe Smith wrote: >> ... >> Note: The cell format does not determine whether a cell holds a numeric >> value or a text value. That is determined by Calc's examination of the data >> when it is entered, and /nothing/ after will change its value type. > > Actually you can change it by editing the cell and removing the apostrophe. > Try it. You're right--what I wrote wasn't very clear. Sorry. I didn't mean that the type of a value can never change, I meant that the only circumstance where the type can change is when the value is entered. And from Calc's point of view, editing a value is no different than entering a value. Either way, when you press Enter, Calc looks at the input and decides whether the input is text or numeric. So, for clarity, I should have written: .../nothing/ after will change its value type, other than a new data entry. Anything that causes Calc to re-examine the input can cause the value type to change, including a search and replace--even a search and replace that doesn't change the value at all. OTOH, changing the cell formatting will never change the value's type, even though it may change the value's appearance. <Joe --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
