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?

I posted this with the intention that somebody from Microsoft will confirm
it's a bug. I think it's really silly... so am glad you agreed! :D

"Stefi" wrote:

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?

Melissa, Microsoft is a colossus on clay legs, it reacts very slowly. We have
to survive day by day, therefore I'd rather like to know that could you come
around the problem?
Stefi


"Melissa" wrote:

I posted this with the intention that somebody from Microsoft will confirm
it's a bug. I think it's really silly... so am glad you agreed! :D

"Stefi" wrote:

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?

Yes, I had to manually update the format of the "errant" cells by:
1. Copying a blank cell
2. Selecting the errant cell, and
3. Paste Special Add

No idea why it works but it did (thanks to other replies to my post).

"Stefi" wrote:

Melissa, Microsoft is a colossus on clay legs, it reacts very slowly. We have
to survive day by day, therefore I'd rather like to know that could you come
around the problem?
Stefi


"Melissa" wrote:

I posted this with the intention that somebody from Microsoft will confirm
it's a bug. I think it's really silly... so am glad you agreed! :D

"Stefi" wrote:

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?

Thanks for your reply! If you want to fix a single cell, just select it,
click on the formula bar and click the tick mark, this method also works on a
single cell. Interesting, isn't it?
Stefi


"Melissa" wrote:

Yes, I had to manually update the format of the "errant" cells by:
1. Copying a blank cell
2. Selecting the errant cell, and
3. Paste Special Add

No idea why it works but it did (thanks to other replies to my post).

"Stefi" wrote:

Melissa, Microsoft is a colossus on clay legs, it reacts very slowly. We have
to survive day by day, therefore I'd rather like to know that could you come
around the problem?
Stefi


"Melissa" wrote:

I posted this with the intention that somebody from Microsoft will confirm
it's a bug. I think it's really silly... so am glad you agreed! :D

"Stefi" wrote:

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?