excel paste/numbers not stored as text

consider

b1=value(a1)

rather than trying to "fix" the copy/paste step, just wrap the formula around the VALUE() function so the formulas can handle numbers-as-text (passing a number into VALUE just returns the number so that's not a problem).

Originally Posted by ChemistB

Are you doing a straight "paste" and not a "paste values"? What are the cells formatted as before anything is pasted into them? It should paste them as numbers unless Excel sees them as text for some reason. The most likely reason is spaces before or after. Is that the case? I can't think of anyway to override this unless you move the spaces before hand. What method are you using to convert them back to numbers?

The results are the same whether I use paste, or paste values. The destination cells are formatted as numbers out to 7 decimals. I can convert them to numbers after they're pasted by clicking the option "convert to number" when the cell is highlighted.
The issue appears to be with the source data, which is formatted as text in order to keep the integrity of the data format. In other words a cell which would have the number "02" is formatted as text, because excel would otherwise shorten this to "2".

Originally Posted by ben_hensel

consider

b1=value(a1)

rather than trying to "fix" the copy/paste step, just wrap the formula around the VALUE() function so the formulas can handle numbers-as-text (passing a number into VALUE just returns the number so that's not a problem).

Ben, great suggestion. I tried this but If some of the data is not a number, or a mixture of letters and numbers it returns the "#VALUE" error. This would work like a charm if I could count on all my data being just numbers. Unfortunately I have a mix.

Originally Posted by ChemistB

If(iserror(value(a1)),a1,value(a1))

BOOM! that's the stuff Chemist!

thanks so much