vba code for formating cell values to numbers

[bahllr]

I have a huge dataset in columns b of mulitple worksheets that are six digit values.

When I try to manipulate them (add together, multiply etc) I get "#VALUE" or "#N/A". If I retype a value over a cell, the manipulations work so it leads me to believe they aren't formatted as values.

I have already tried to format cells as numbers through excel but that doesn't work. Is there code that I can use/excel tips out there to make these numbers numbers!!! So I don't have to do it all by hand?!

Thanks!

 

[Norie]

You could try using Data>Text to columns...

Formatting isn't actually going to change the values in the cells - it'll just change the appearance.

 

[bahllr]

Norie,

I have tried text to column without any success....any other ideas?

<table x:str="" style="border-collapse: collapse; width: 94pt;" border="0" cellpadding="0" cellspacing="0" width="125"><col style="width: 94pt;" width="125"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 94pt;" width="125" height="20">547379 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt; width: 94pt;" width="125" height="20">547420 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt; width: 94pt;" width="125" height="20">547452 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" x:err="#VALUE!" x:fmla="=A3+1" height="20">#VALUE!</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">equals 547452+1</td> </tr> </tbody></table>