Excel - how to sort number properly in excel?

Asked By olimerg on 29-Oct-07 01:12 PM
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




Tim Zych replied on 29-Oct-07 01:22 PM
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
olimerg replied on 29-Oct-07 01:26 PM
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.
fdh replied on 29-Oct-07 01:26 PM
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
Nick Hodge replied on 29-Oct-07 01:26 PM
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 Edit>Paste 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
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog
Gord Dibben replied on 29-Oct-07 01:30 PM
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 Edit>Paste Special>Add>OK>Esc


Gord Dibben  MS Excel MVP

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