Numbers stored as text prevent calculations

5

525047

We receive XLS files which contain columns of numbers which we then us
to calculate other fields. The columns should just contain numbers bu
some (randomly) seem to have been created as numbers in text forma
(i.e. it shows the number zero). If you try and test for them bein
zero it fails e.g.

A1=0 (or what looks like zero!)
=if(a1=0,true,false) gives false

if you re-type over A1 with a zero number on the keyboard it works
Excel flags the original data as being held in character format. Usin
FORMAT CELL to change the zero to GENERAL or NUMBER has no effect o
the test failing.

How could the zero number have been created as character format an
whats the best way to prevent it?
Is there any way to automatically convert any rows in this state t
true numbers?

We are using Excel 2003 SP
 
M

MartinW

Hi 525047,

Try this put 1 into a blank cell then copy it, then select all
your data and choose paste special>check multiply option
and OK. That will convert anything that is text to a number.

HTH
Martin
 
G

Gord Dibben

Format all to General.

Copy an empty cell and select your range of numbers.

Edit>Paste Special>Add>OK>Esc.

Should be good to go.

Prevention would be at the sending end of the cycle I suppose.

Make sure all is numeric before you receive the files.


Gord Dibben MS Excel MVP
 
M

MartinW

Hi Gord,

Just for interest sake. Is there any reason for you using add 0
rather than multiply by 1? Or is it just a toss of the coin either
either type thing?

Not being picky just interested to know if there is some situations
where one way would work better than the other.

Regards
Martin
 
D

Dave Peterson

I like copying an empty cell so that I can select a range that includes empty
cells.

Watch the difference when you multiply by 1 or add 0 (not just an empty cell).
 
M

MartinW

Thanks Dave,

I see your point. Add is the better option for sure.
I hope the OP is happy I seem to have hijacked his thread!

Regards
Martin
 
G

Gord Dibben

Martin

Dave has explained his and my reason for the empty cell and "add".

Also, you don't have to go and clear contents on the cell after the paste.

On the other hand..................

I think it was RagDyer who suggested that a seemingly empty cell could contain a
space or two and screw up the operation so that is something to be wary of.

I have been fortunate enough to never encounter that problem.


Gord Dibben MS Excel MVP
 
D

Dave Peterson

Adding the empty cell is my method of choice--as opposed to adding a cell with
0.

(Just to stress a minor point.)
 
M

MartinW

Yes Dave,

You are dealing with semantics here. When I said add 0
that is the effect that copying a blank cell has.

Regards
Martin
 
D

Dave Peterson

Yep. But adding 0 (a cell with 0) to an empty cell is not the same.

And yes, it is a minor point--but I thought it important to make the
distinction.
 
Top