I have a column containing numbers 1-40. I made sure the format of these
cells is "Number" then I sorted my spreadsheet in ascending order. The
strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
after 40. Why is this happening?

Are you absolutely sure of the format? Sounds like the errant values are text.

Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
sort it will select the current region which is determined by where xl finds blank rows
and columns.
To be sure, select the values and then sort.

HTH
--
Regards;
Rob
------------------------------------------------------------------------
"Melissa" wrote in message
...
I have a column containing numbers 1-40. I made sure the format of these
cells is "Number" then I sorted my spreadsheet in ascending order. The
strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
after 40. Why is this happening?

I expect that when I select my whole column and format cells to Number, that
all the selected cells WILL have the format: Number.

In response to your question on breaks, my spreadsheet does not contain any
empty rows although somes cells may be blank.

I selected all the relevant rows and columns before applying auto-filter.

A bit more info on this column that I am sorting: this column contains
numbers 1-40 plus some blank cells.

"RWN" wrote:

Are you absolutely sure of the format? Sounds like the errant values are text.

Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
sort it will select the current region which is determined by where xl finds blank rows
and columns.
To be sure, select the values and then sort.

HTH
--
Regards;
Rob
------------------------------------------------------------------------
"Melissa" wrote in message
...
I have a column containing numbers 1-40. I made sure the format of these
cells is "Number" then I sorted my spreadsheet in ascending order. The
strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
after 40. Why is this happening?

I expect that when I select my whole column and format cells to Number, that
all the selected cells WILL have the format: Number.

Not necessarily. If a text value is already present then selecting the cell and changing
the format won't have any effect (try it).
To force the format to a number, select a blank cell and copy it. Then select the range
where you require the format to be numeric and "PasteSpecial-Add".


--
Regards;
Rob
------------------------------------------------------------------------
"Melissa" wrote in message
...
I expect that when I select my whole column and format cells to Number, that
all the selected cells WILL have the format: Number.

In response to your question on breaks, my spreadsheet does not contain any
empty rows although somes cells may be blank.

I selected all the relevant rows and columns before applying auto-filter.

A bit more info on this column that I am sorting: this column contains
numbers 1-40 plus some blank cells.

"RWN" wrote:

Are you absolutely sure of the format? Sounds like the errant values are text.

Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
sort it will select the current region which is determined by where xl finds blank
rows
and columns.
To be sure, select the values and then sort.

HTH
--
Regards;
Rob
------------------------------------------------------------------------
"Melissa" wrote in message
...
I have a column containing numbers 1-40. I made sure the format of these
cells is "Number" then I sorted my spreadsheet in ascending order. The
strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
after 40. Why is this happening?

I checked the format of the "errant" cells and it shows Number too.

In future, how will I know that there's an "underlying" text format if I
can't see it in the FormatCell menu? It so happens I noticed the problem in
this spreadsheet, which is also a small file.

BTW, I did the pasteSpecial Add and my column is sorting correctly now.
Can you please explain what this "Add" operation does?

Many thanks.

"RWN" wrote:

I expect that when I select my whole column and format cells to Number, that
all the selected cells WILL have the format: Number.

Not necessarily. If a text value is already present then selecting the cell and changing
the format won't have any effect (try it).
To force the format to a number, select a blank cell and copy it. Then select the range
where you require the format to be numeric and "PasteSpecial-Add".


--
Regards;
Rob
------------------------------------------------------------------------
"Melissa" wrote in message
...
I expect that when I select my whole column and format cells to Number, that
all the selected cells WILL have the format: Number.

In response to your question on breaks, my spreadsheet does not contain any
empty rows although somes cells may be blank.

I selected all the relevant rows and columns before applying auto-filter.

A bit more info on this column that I am sorting: this column contains
numbers 1-40 plus some blank cells.

"RWN" wrote:

Are you absolutely sure of the format? Sounds like the errant values are text.

Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
sort it will select the current region which is determined by where xl finds blank
rows
and columns.
To be sure, select the values and then sort.

HTH
--
Regards;
Rob
------------------------------------------------------------------------
"Melissa" wrote in message
...
I have a column containing numbers 1-40. I made sure the format of these
cells is "Number" then I sorted my spreadsheet in ascending order. The
strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
after 40. Why is this happening?

Oh, and why won't Format Painter work? I thought it's to apply the format to
the new cells?

"RWN" wrote:

I expect that when I select my whole column and format cells to Number, that
all the selected cells WILL have the format: Number.

Not necessarily. If a text value is already present then selecting the cell and changing
the format won't have any effect (try it).
To force the format to a number, select a blank cell and copy it. Then select the range
where you require the format to be numeric and "PasteSpecial-Add".


--
Regards;
Rob
------------------------------------------------------------------------
"Melissa" wrote in message
...
I expect that when I select my whole column and format cells to Number, that
all the selected cells WILL have the format: Number.

In response to your question on breaks, my spreadsheet does not contain any
empty rows although somes cells may be blank.

I selected all the relevant rows and columns before applying auto-filter.

A bit more info on this column that I am sorting: this column contains
numbers 1-40 plus some blank cells.

"RWN" wrote:

Are you absolutely sure of the format? Sounds like the errant values are text.

Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
sort it will select the current region which is determined by where xl finds blank
rows
and columns.
To be sure, select the values and then sort.

HTH
--
Regards;
Rob
------------------------------------------------------------------------
"Melissa" wrote in message
...
I have a column containing numbers 1-40. I made sure the format of these
cells is "Number" then I sorted my spreadsheet in ascending order. The
strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
after 40. Why is this happening?

"Melissa" wrote:
.. I expect that when I select my whole column
and format cells to Number, that
all the selected cells WILL have the format: Number.

Think formatting doesn't change the underlying ..

Try this:

Copy an empty cell, then select the range of 40 numbers
Right-click paste special Check "Add" OK

Now the 40 numbers should sort properly
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

What is the source of these data? I experienced earlier that imported data
(e.g. from a text file) behave like that!

Stefi


"Melissa" wrote:

I expect that when I select my whole column and format cells to Number, that
all the selected cells WILL have the format: Number.

In response to your question on breaks, my spreadsheet does not contain any
empty rows although somes cells may be blank.

I selected all the relevant rows and columns before applying auto-filter.

A bit more info on this column that I am sorting: this column contains
numbers 1-40 plus some blank cells.

"RWN" wrote:

Are you absolutely sure of the format? Sounds like the errant values are text.

Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
sort it will select the current region which is determined by where xl finds blank rows
and columns.
To be sure, select the values and then sort.

HTH
--
Regards;
Rob
------------------------------------------------------------------------
"Melissa" wrote in message
...
I have a column containing numbers 1-40. I made sure the format of these
cells is "Number" then I sorted my spreadsheet in ascending order. The
strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
after 40. Why is this happening?

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?

"Stefi" wrote:

What is the source of these data? I experienced earlier that imported data
(e.g. from a text file) behave like that!

Stefi


"Melissa" wrote:

I expect that when I select my whole column and format cells to Number, that
all the selected cells WILL have the format: Number.

In response to your question on breaks, my spreadsheet does not contain any
empty rows although somes cells may be blank.

I selected all the relevant rows and columns before applying auto-filter.

A bit more info on this column that I am sorting: this column contains
numbers 1-40 plus some blank cells.

"RWN" wrote:

Are you absolutely sure of the format? Sounds like the errant values are text.

Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
sort it will select the current region which is determined by where xl finds blank rows
and columns.
To be sure, select the values and then sort.

HTH
--
Regards;
Rob
------------------------------------------------------------------------
"Melissa" wrote in message
...
I have a column containing numbers 1-40. I made sure the format of these
cells is "Number" then I sorted my spreadsheet in ascending order. The
strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
after 40. Why is this happening?

In my opinion it's an Excel bug. I found a workaround: format a new helper
column as number, copy your misbehaving column and paste special values in
the helper column. Try to sort by it and if it is correct, you may copy it
back in its original place if necessary.

Regards,
Stefi


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

"Stefi" wrote:

What is the source of these data? I experienced earlier that imported data
(e.g. from a text file) behave like that!

Stefi


"Melissa" wrote:

I expect that when I select my whole column and format cells to Number, that
all the selected cells WILL have the format: Number.

In response to your question on breaks, my spreadsheet does not contain any
empty rows although somes cells may be blank.

I selected all the relevant rows and columns before applying auto-filter.

A bit more info on this column that I am sorting: this column contains
numbers 1-40 plus some blank cells.

"RWN" wrote:

Are you absolutely sure of the format? Sounds like the errant values are text.

Also, are the rows contiguous (i.e. no breaks)? If there is an empty row and you click
sort it will select the current region which is determined by where xl finds blank rows
and columns.
To be sure, select the values and then sort.

HTH
--
Regards;
Rob
------------------------------------------------------------------------
"Melissa" wrote in message
...
I have a column containing numbers 1-40. I made sure the format of these
cells is "Number" then I sorted my spreadsheet in ascending order. The
strange thing is, the order is correct except that 19, 35, 36, 38, 39 appear
after 40. Why is this happening?

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