Sorting by only numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

George - 21 Jul 2008 13:56 GMT

How to sort ignoring letters, Excel 2003
ny only containing numbers,
For example
default sorting wanted sorting
AA101 AC02
AB99 AB99
AC02 AA101
Thanks
George

Reply to this Message

Dave Peterson - 21 Jul 2008 14:07 GMT

You'll have to separate the numbers into a different column.

If all the letters are exactly two digits, you could use:
=--mid(a1,3,255)

(the -- converts the text that =mid() produces to a real number.)

Then you can sort your data by using this helper column.

> How to sort ignoring letters, Excel 2003
> ny only containing numbers,
[quoted text clipped - 5 lines]
> Thanks
> George

Signature

Dave Peterson

Reply to this Message

George - 24 Jul 2008 13:19 GMT

Thank you for your response,

Unfortunately the letters ranging from 2 to 8 Approx, Is there a macro for
it?

> You'll have to separate the numbers into a different column.
>
[quoted text clipped - 14 lines]
>> Thanks
>> George

Reply to this Message

George - 24 Jul 2008 13:20 GMT

Thank you for your response,

Unfortunately the letters ranging from 2 to 8 Approx, Is there a macro for
it?

> You'll have to separate the numbers into a different column.
>
[quoted text clipped - 14 lines]
>> Thanks
>> George

Reply to this Message

Dave Peterson - 26 Jul 2008 03:37 GMT

You could use a formula like:
=--RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)
to get the rightmost group of digits.

> You'll have to separate the numbers into a different column.
>
[quoted text clipped - 18 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message