How to COnvert text string to number

M

Mike Thomas

In Excel 2000, I have a spreadsheet populated with values exported from
Access. There are 4 columns which, although they look like numbers, must
really be text type. When trying to sum the columns, I am coming up with a
sum of zero, although I can clearly see the numbers in the range of the sum
function.

Trying to reformat them as numbers seems to have no effect. None of the
cells in the column have any characters in the cell, although some of them
are empty (null?).

How can I turn these columns into number so I can sum the cells?

Many thanks
Mike Thomas
 
J

JS

if you mulitply the numbers in the column by 1 (in the column next to them)
they should convert to number format. Or use the following macro

Sub TextToNum()
dim cell as range
for each cell in selection
cell.value = cell.value
next cell
End Sub

Select the columns and run the macro
 
G

Gord Dibben

Mike

Copy an empty cell.

Select your range of "numbers".

Paste Special>Add>OK>Esc

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
A

Andy

Mike

Make sure the 'empty cell' you copy is not formatted as text.

Andy.
Gord Dibben said:
Mike

Copy an empty cell.

Select your range of "numbers".

Paste Special>Add>OK>Esc

Gord Dibben Excel MVP - XL97 SR2 & XL2002
Click to expand...
 
T

timtak

Thanks.

I did not know the meaning of "special paste > add"

But it would be nice to know how to do the same with an excel function


Is there something like
=num(a1)
which would turn a string, even a string, even a null string into t
number?

There is no need to answer my post since I used the paste functio
suggested.
But to say thank you and for the sake of future viewers I leave you al
with this
question.

Thanks again,
Ti
 
T

TomHinkle

I just use the following

If my 'text' number is in A1

in B1 I have the formula

= A1 + 0

works like a charm..
 
A

Anki

If you know the target cell(s) can be converted to a number (i.e. it is not a
mix of text and number), you can simply use the value function: VALUE(C2) if
C2 contains your number in text format.
 
Top