how to sort number properly in excel?
 

I notice that unlike Access, Excel doesnt sort numbers in their true
numerical sequence.

e.g. a varying digit ID field I have ends up sorted as such:

1008
1009
101
1010
1011
etc.

Obviously 101 shouldnt be between these values, it should be way before
these particular ones.

The column is formatted as 0 dp numbers.

Is there a simple trick with formatting/ options etc. that will let me see
the results in true numerical order? I wondered about forcing them to all
have the same amount of digits with custom format maybe...

Thanks in advance

how to sort number properly in excel?
 

I'm guessing they're still text becasuse Excel certainly does sort numbers
properly. Excel sorts based on the underlying value, not the format.
Numberformatting doesn't change the underlying data type, it just changes
the look of the real value.
Numbers in Excel are right-aligned by default, text is left-aligned by
default.
To convert to numeric values you can type a 1 in a cell, copy it, select the
numbers, PasteSpecial/Multiply.


--
Tim Zych
SF, CA

"oli merge" wrote in message
...
I notice that unlike Access, Excel doesnt sort numbers in their true
numerical sequence.

e.g. a varying digit ID field I have ends up sorted as such:

1008
1009
101
1010
1011
etc.

Obviously 101 shouldnt be between these values, it should be way before
these particular ones.

The column is formatted as 0 dp numbers.

Is there a simple trick with formatting/ options etc. that will let me see
the results in true numerical order? I wondered about forcing them to all
have the same amount of digits with custom format maybe...

Thanks in advance

how to sort number properly in excel?
 

Hi,

Do you see a small green triangle in the top left corner of the cells? if
yes then you should convert your data to numbers just select the numbers you
want to convert then you can see a small rectangle with the symbol "!" in the
beginning cell of your selection just click on it and chose convert to
numbers and then sort your data.

Thanks,
--
Farhad Hodjat


"oli merge" wrote:

I notice that unlike Access, Excel doesnt sort numbers in their true
numerical sequence.

e.g. a varying digit ID field I have ends up sorted as such:

1008
1009
101
1010
1011
etc.

Obviously 101 shouldnt be between these values, it should be way before
these particular ones.

The column is formatted as 0 dp numbers.

Is there a simple trick with formatting/ options etc. that will let me see
the results in true numerical order? I wondered about forcing them to all
have the same amount of digits with custom format maybe...

Thanks in advance

how to sort number properly in excel?
 

Ok, after a bit more experimenting it seems that this only happens if the
column being sorted contains non-numeric entries too.

When I removed them Excel sorted it properly.

how to sort number properly in excel?
 

Oli

This is because Excel is not seeing them as numbers but text (Naturally they
will be left aligned???)

If you enter a 1 in a blank cell and copy it. Then highlight these 'numbers'
and do an EditPaste special...Values+Multiply. It will force Excel to see
them as numbers. Now re-sort

Be aware that if you have any Part numbers with leading zeros these to will
be lost as a true number doesn't need these

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog


"oli merge" wrote in message
...
I notice that unlike Access, Excel doesnt sort numbers in their true
numerical sequence.

e.g. a varying digit ID field I have ends up sorted as such:

1008
1009
101
1010
1011
etc.

Obviously 101 shouldnt be between these values, it should be way before
these particular ones.

The column is formatted as 0 dp numbers.

Is there a simple trick with formatting/ options etc. that will let me see
the results in true numerical order? I wondered about forcing them to all
have the same amount of digits with custom format maybe...

Thanks in advance

how to sort number properly in excel?
 

Your numbers are not real numbers.

They are Text.

Formatting to number will not change that fact.

Format all to General then copy an empty cell.

Select the "numbers" and in place EditPaste SpecialAddOKEsc


Gord Dibben MS Excel MVP

On Mon, 29 Oct 2007 10:12:00 -0700, oli merge
wrote:

I notice that unlike Access, Excel doesnt sort numbers in their true
numerical sequence.

e.g. a varying digit ID field I have ends up sorted as such:

1008
1009
101
1010
1011
etc.

Obviously 101 shouldnt be between these values, it should be way before
these particular ones.

The column is formatted as 0 dp numbers.

Is there a simple trick with formatting/ options etc. that will let me see
the results in true numerical order? I wondered about forcing them to all
have the same amount of digits with custom format maybe...

Thanks in advance