how to sort number properly in excel?

[oli merge]

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]

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.

 

[oli merge]

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.

 

[Farhad]

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,

 

[Nick Hodge]

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
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog

 

[Gord Dibben]

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