Numbers stored as text prevent calculations

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

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

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


On Wed, 16 Aug 2006 00:44:59 +0100, 525047 <[email protected]>
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

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

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).

MartinW wrote:
>
> 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

--

Dave Peterson

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

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

On Wed, 16 Aug 2006 12:22:06 +1000, "MartinW" <[email protected]> wrote:

>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
>

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

(Just to stress a minor point.)

MartinW wrote:
>
> 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

--

Dave Peterson

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

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.

MartinW wrote:
>
> 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

--

Dave Peterson