On Tue, 11 Oct 2005 23:15:03 -0700, "Melissa"
wrote:

Yes, the values were imported. But the question is: why after formatting the
cells to Number, are they still not behaving the way they should?

You are confusing cell format with cell contents. They are really two
different things.

Changing the format of a cell does not change its contents.

If you have already entered TEXT into a cell, changing the format does not
change the nature of the Value that is stored.

For many operations, Excel will give you the option of treating TEXT that looks
like numbers, as numbers. You should get that option using the Data/Sort
wizard (at least in versions 2002 and subsequent; I can't recall about before
then).

There are several ways to tell the difference. You can use the formula
=ISTEXT(cell_ref); if you have not changed the default justification, TEXT
will be left-justified and numbers will be right-justified.


--ron

Hi Ron,

I am completely aware of the difference between cell format and cell
content, but the problem is that in these cases data are NOT ENTERED via
typing into the cells but are imported from a .txt file. I use Excel 2000 and
have no experience with 2002 and above (I intend to test this phenomenon in
Xl2003 in the near future), but in Xl2000 it definitely happens that sorting
either numeric or text columns imported from a text file (all in General
format) gives incorrect result. The same is the case with autofiltering.

For some unkown reasons Xl2000 "senses" the real value of these cells
contents only after manipulations written earlier in this thread. Note, that
these operations don't deal with formatting (because formatting really
doesn't fix the problem)!

Regards,
Stefi


"Ron Rosenfeld" wrote:

On Tue, 11 Oct 2005 23:15:03 -0700, "Melissa"
wrote:

Yes, the values were imported. But the question is: why after formatting the
cells to Number, are they still not behaving the way they should?

You are confusing cell format with cell contents. They are really two
different things.

Changing the format of a cell does not change its contents.

If you have already entered TEXT into a cell, changing the format does not
change the nature of the Value that is stored.

For many operations, Excel will give you the option of treating TEXT that looks
like numbers, as numbers. You should get that option using the Data/Sort
wizard (at least in versions 2002 and subsequent; I can't recall about before
then).

There are several ways to tell the difference. You can use the formula
=ISTEXT(cell_ref); if you have not changed the default justification, TEXT
will be left-justified and numbers will be right-justified.


--ron

On Wed, 12 Oct 2005 05:50:02 -0700, "Stefi"
wrote:

Hi Ron,

I am completely aware of the difference between cell format and cell
content, but the problem is that in these cases data are NOT ENTERED via
typing into the cells but are imported from a .txt file. I use Excel 2000 and
have no experience with 2002 and above (I intend to test this phenomenon in
Xl2003 in the near future), but in Xl2000 it definitely happens that sorting
either numeric or text columns imported from a text file (all in General
format) gives incorrect result. The same is the case with autofiltering.

For some unkown reasons Xl2000 "senses" the real value of these cells
contents only after manipulations written earlier in this thread. Note, that
these operations don't deal with formatting (because formatting really
doesn't fix the problem)!

Regards,
Stefi

Was there any implication that *you* were not aware of the difference between
format and value?

It is clear from the top of my message that my comments were directed towards a
statement by Melissa.

She was the poster who did not understand why changing the formatting did not
help her problem.

The reason XL2000 "senses the real value" is not "unknown". Rather it is
because the "real value" is changed by the manipulations you outlined from
being TEXT to being NUMERIC.


--ron

I don't profess to be an expert but I *believe* the problem arises because the imported
text data lacks a sign byte.
Although it's a pain I don't think it is a "bug" (I run into the same problem uploading
data to an I-Series).
I've found that whenever I have to troubleshoot this sort of thing I enlarge the column
width and kill any justification.
The result is that the "text" numbers will be left justified and the "valid" numbers will
be right justified.

At work I use xl'03 and must say that I appreciate the warning I get from xl about
detecting mixed formats-saves a lot of time when my "Vlookups" return #N/A's!

--
Regards;
Rob
------------------------------------------------------------------------
"Ron Rosenfeld" wrote in message
...
On Wed, 12 Oct 2005 05:50:02 -0700, "Stefi"
wrote:

Hi Ron,

I am completely aware of the difference between cell format and cell
content, but the problem is that in these cases data are NOT ENTERED via
typing into the cells but are imported from a .txt file. I use Excel 2000 and
have no experience with 2002 and above (I intend to test this phenomenon in
Xl2003 in the near future), but in Xl2000 it definitely happens that sorting
either numeric or text columns imported from a text file (all in General
format) gives incorrect result. The same is the case with autofiltering.

For some unkown reasons Xl2000 "senses" the real value of these cells
contents only after manipulations written earlier in this thread. Note, that
these operations don't deal with formatting (because formatting really
doesn't fix the problem)!

Regards,
Stefi

Was there any implication that *you* were not aware of the difference between
format and value?

It is clear from the top of my message that my comments were directed towards a
statement by Melissa.

She was the poster who did not understand why changing the formatting did not
help her problem.

The reason XL2000 "senses the real value" is not "unknown". Rather it is
because the "real value" is changed by the manipulations you outlined from
being TEXT to being NUMERIC.


--ron

Hi Ron,

I didn't take upon myself your comment, although I really didn't notice that
your comment is addressed to Melissa, sorry. I simply wanted to point out
that before any formatting all cells format are "General" in a new sheet, let
it be populated with either manually entered or imported data, and still
there is a difference between the ways XL2000 handles them. Their format are
the same (General), their value look identical, therefore it is misleading
that you have to manipulate (not format) the imported ones in order to be
handled in a normal way.

To Rob! It's a matter of taste to consider this a bug or not.

Regards,
Stefi