Data Type Problem?

[bailey]

I have a spreadsheet that was given to me but I cannot do anything with it. I cannot perform simple functions, if I do I receive the #Value error or a zero, if I try to paste special values only the same thing happens. If I try to change the formats nothing actually changes. I have tried copy and pasting into different formats and programs and then pasting into a new Excel doc, none of this has worked. I believe the data came from a web reporting interface that pulled data from a sequel database. Any advice would be greatly apprecaited. bailey mg

[Bryan Hessey]

If this is a single sheet perhaps save it to a .csv file, and start over with your formula.

I have a spreadsheet that was given to me but I cannot do anything with it. I cannot perform simple functions, if I do I receive the #Value error or a zero, if I try to paste special values only the same thing happens. If I try to change the formats nothing actually changes. I have tried copy and pasting into different formats and programs and then pasting into a new Excel doc, none of this has worked. I believe the data came from a web reporting interface that pulled data from a sequel database. Any advice would be greatly apprecaited. bailey mg

[bailey]

thanks, but I have tried this a few times and it doesn't work. I have also assumed that the "wrong data type" is text and so I have followed the instructions to change text to a number but that has not worked either. Any other ideas? I really appreciate your help.

[Bryan Hessey]

To extract the number from text is =value(A1) (in a separate column) then copy and Paste Special, Values back over the A column (and delete the helper column created)

thanks, but I have tried this a few times and it doesn't work. I have also assumed that the "wrong data type" is text and so I have followed the instructions to change text to a number but that has not worked either. Any other ideas? I really appreciate your help.

[bailey]

Bryan,

thanks, but it isn't recognizing the value. Thus, instead of providing a value, I get the #value! error. I have also tried both methods that are in the Excel tips section of exceltip.com but to no avail.

[Bryan Hessey]

can you disguise the data and post a few lines?

Bryan,

thanks, but it isn't recognizing the value. Thus, instead of providing a value, I get the #value! error. I have also tried both methods that are in the Excel tips section of exceltip.com but to no avail.

[bailey]

Sure, If I'm following correctly, here it is:

145656* 53440* $20,980,279.65* $144.04*
92275* 35679* $10,417,074.43* $112.89*
90635* 34930* $8,685,315.87* $95.83*
87643* 31368* $11,959,992.26* $136.46*
83080* 28001* $12,381,818.36* $149.03*
70926* 26256* $9,921,110.03* $139.88*

[Bryan Hessey]

try =LEFT(A1,LEN(A1)-1) to remove the *

note, for multiple columns A to D you can put that in E1, formula drag it to H1, then select E1:H1 and formula drag downwards for all rows (assumung E1:H1 are a free range)

Sure, If I'm following correctly, here it is:

145656* 53440* $20,980,279.65* $144.04*
92275* 35679* $10,417,074.43* $112.89*
90635* 34930* $8,685,315.87* $95.83*
87643* 31368* $11,959,992.26* $136.46*
83080* 28001* $12,381,818.36* $149.03*
70926* 26256* $9,921,110.03* $139.88*

[bailey]

Bryan,

Thanks, the fact that the * revealed itself was a big help in itself. I could not get the formula to work, but could remove the manually by backspacing in the formula bar. The problem is I have thousands of them. Any other ideas? Thanks again for your help!

-Bailey