Numbers stored as text prevent calculations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

525047 - 16 Aug 2006 00:44 GMT

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

--
525047

Reply to this Message

MartinW - 16 Aug 2006 02:29 GMT

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

Reply to this Message

Gord Dibben - 16 Aug 2006 02:35 GMT

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

>We receive XLS files which contain columns of numbers which we then use
>to calculate other fields. The columns should just contain numbers but
[quoted text clipped - 16 lines]
>
>We are using Excel 2003 SP2

Reply to this Message

MartinW - 16 Aug 2006 03:22 GMT

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

Reply to this Message

Dave Peterson - 16 Aug 2006 03:39 GMT

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

> Hi Gord,
>
[quoted text clipped - 7 lines]
> Regards
> Martin

Signature

Dave Peterson

Reply to this Message

MartinW - 16 Aug 2006 03:55 GMT

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

Reply to this Message

Dave Peterson - 16 Aug 2006 13:57 GMT

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

(Just to stress a minor point.)

> Thanks Dave,
>
[quoted text clipped - 3 lines]
> Regards
> Martin

Signature

Dave Peterson

Reply to this Message

MartinW - 16 Aug 2006 15:02 GMT

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

Reply to this Message

Dave Peterson - 16 Aug 2006 17:12 GMT

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.

> Yes Dave,
>
[quoted text clipped - 3 lines]
> Regards
> Martin

Signature

Dave Peterson

Reply to this Message

Gord Dibben - 16 Aug 2006 05:43 GMT

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

>Hi Gord,
>
[quoted text clipped - 7 lines]
>Regards
>Martin

Reply to this Message