We receive XLS files which contain columns of numbers which we then use
to calculate other fields. The columns should just contain numbers but
some (randomly) seem to have been created as numbers in text format
(i.e. it shows the number zero). If you try and test for them being
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. Using
FORMAT CELL to change the zero to GENERAL or NUMBER has no effect on
the test failing.

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

We are using Excel 2003 SP2




--
525047