Data export - cell formats

Posted by Todd on November 07, 2001 7:37 AM

Hello,

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?

Thanks,

Todd

Posted by Aladin Akyurek on November 07, 2001 7:51 AM

> 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.

Todd -- Where A1+A2+3 returns a correct result, while SUM returns 0 indicates that the cells are text-formatted. Those +'s causes Excel to convert text-formatted numbers to numbers. SUM simply ignores text.

I'd suggest to copy an empty cell (whose format is General), select the problem range of interest, activate Edit|Paste Special, and check Add.

Curious whether this will help.

Aladin

Posted by Todd on November 07, 2001 10:54 AM

Aladin,

Hey. That seemed to do the trick... I needed these data values to be referenced in another sheet of the same workbook, so I also tried using the VALUE function e.g. =VALUE(C3)

This returned a numerical value that I could actually use in any formulas I needed.

Thanks for your help! This is a great forum!

Todd

Posted by Aladin Akyurek on November 07, 2001 2:06 PM

+0 ...

Instead of using the VALUE function in your formulas to handle the problem, you can just use:

=C3+0

This should be faster.

================

Posted by Todd on November 08, 2001 6:49 AM

Re: +0 ...

Hi,

I saw that from another posting you made for someone else earlier on... and kind of figured it out when you mentioned that a simple operator like "+" forces Excel to treat a value as a number.

Thanks again!

Todd
========================