vba code for formating cell values to numbers

bahllr

Board Regular
Joined
May 7, 2009
Messages
62
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!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,104
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 7, 2009
Messages
62
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,119,277
Messages
5,577,177
Members
412,773
Latest member
ely6678
Top