Format all to General.

Copy an empty cell and select your range of numbers.

EditPaste SpecialAddOKEsc.

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


On Wed, 16 Aug 2006 00:44:59 +0100, 525047
wrote:


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