Sorting negative numbers?

[pearce]

I'm trying to sort a column of negative and positive numbers in excel 2003
but nothing seems to be happening. I've tried changing the type of cell but
haven't got anywhere, can anyone help?

e.g. after sorting I still have:

-6
-4
-2
-2
-6
0 etc

 

[Mike H]

Hi,

Do you get a 'Sort Warning' when trying to do your sort?
If yes then select 'Sort anything that looks like a number as a number' and
try again. Excel should manage to sort them even if they are text.

If they are text and you want to change then to numbers put a one (1) in a
cell and copy it. Select your 'numbers' and then
Edit|Paste special
Select multiply and click OK.

Mike

Mike

 

[pearce]

I'm still struggling with this one. I don't get any warning it's just not
doing what it's asked to when I do data - sort. I've formated the cells to be
number cells but it's still leaving a random order of negative numbers - help!

 

[David Biddulph]

Just formatting the cells as number doesn't change existing text to a
number. Format cells can change how a number is displayed, but it doesn't
change the underlying value. If the underlying value is text it will stay
as text. Check with
=ISNUMBER(cell_ref) and
=ISTEXT(cell_ref)

If it is text, you'll need to change it to a number before it will sort as a
number. It might be worth trying Data/ Text to columns. If you are lucky,
that might change the text to numbers.

 

[Gord Dibben]

Changing the format does nothing if the cells are text.

Format all to general then follow Mike's advice about the paste special bit.


Gord Dibben MS Excel MVP