Data export - cell formats
Posted by Todd on November 07, 2001 7:37 AM
I'm pulling my hair out on this one. I regularly export data from a Crystal Report into Excel 2000, and now need to perform mathematical operations on that data. When the data is imported, every cell seems to be in "general" format. That's fine, b/c I can easily change it to "number" format (though from reading other similar postings, that affects only how the data looks).
But something strange is happening... Regardless of the cell format, I can't use formulas like SUM or AVERAGE on a group of numbers (SUM always returns a zero, and AVERAGE returns #DIV/0!) yet I can use a simple formula like A1+A2+A3 and it returns the correct result.
I've checked for extra spaces in the cells where the imported data is located - all clean both before and ater the numbers. However, on a hunch I tried editing the cells, moving before the first digit, and hitting BACKSPACE. Voila! I can use the numbers in calculations! There's obviously _something_ in there! I tried using the TRIM and CLEAN functions to rid the cells of extraneous info, but to no avail...
I have too many cells to use in calculations to go in and edit each one... Any suggestions?