sorting in a cell
 

I have a very long list which I would like to sort (see first column example)
first by the last two digits and then the 3rd digit, so that the sorted list
would appear like column 2. I don't know where to start on this.

2297814 2179312
766961 2297814
2200735 2317528
2113780 2005233
2225041 2200735
604875 2225041
2103953 557042
557042 2272153
738066 2103953
2179312 766961
2272153 738066
2224766 2224766
2317528 604875
2126879 2126879
2005233 2113780
2260982 2260982

sorting in a cell
 

For values in column A:
In B1:
=RIGHT(A1,3)
In C1:
=LEFT(B1,1)*1
In D1:
=RIGHT(B1,2)*1

Then copy these cells down. Sort first by column D, then by column C to see:

2179312 312 3 12
2297814 814 8 14
2317528 528 5 28
2005233 233 2 33
2200735 735 7 35
2225041 041 0 41
557042 042 0 42
2272153 153 1 53
2103953 953 9 53
766961 961 9 61
738066 066 0 66
2224766 766 7 66
604875 875 8 75
2126879 879 8 79
2113780 780 7 80
2260982 982 9 82

--
Gary''s Student - gsnu200756


"Kim" wrote:

I have a very long list which I would like to sort (see first column example)
first by the last two digits and then the 3rd digit, so that the sorted list
would appear like column 2. I don't know where to start on this.

2297814 2179312
766961 2297814
2200735 2317528
2113780 2005233
2225041 2200735
604875 2225041
2103953 557042
557042 2272153
738066 2103953
2179312 766961
2272153 738066
2224766 2224766
2317528 604875
2126879 2126879
2005233 2113780
2260982 2260982

sorting in a cell
 

Hi Kim

Assuming your list of numbers is in column A, create a helper column, with
the formula
=RIGHT(A1,2)
and copy down

Mark the whole range of data, then carry out the Sort based upon the helper
column.
You can then delete the helper column.

--
Regards
Roger Govier



"Kim" wrote in message
...
I have a very long list which I would like to sort (see first column
example)
first by the last two digits and then the 3rd digit, so that the sorted
list
would appear like column 2. I don't know where to start on this.

2297814 2179312
766961 2297814
2200735 2317528
2113780 2005233
2225041 2200735
604875 2225041
2103953 557042
557042 2272153
738066 2103953
2179312 766961
2272153 738066
2224766 2224766
2317528 604875
2126879 2126879
2005233 2113780
2260982 2260982

sorting in a cell
 

I wasn't able to sort unless copied cells C and D to new columns as values.
I'll try to see if this does it. I thought I had done it one other time
months ago using a different type of formula. Thanks!

"Gary''s Student" wrote:

For values in column A:
In B1:
=RIGHT(A1,3)
In C1:
=LEFT(B1,1)*1
In D1:
=RIGHT(B1,2)*1

Then copy these cells down. Sort first by column D, then by column C to see:

2179312 312 3 12
2297814 814 8 14
2317528 528 5 28
2005233 233 2 33
2200735 735 7 35
2225041 041 0 41
557042 042 0 42
2272153 153 1 53
2103953 953 9 53
766961 961 9 61
738066 066 0 66
2224766 766 7 66
604875 875 8 75
2126879 879 8 79
2113780 780 7 80
2260982 982 9 82

--
Gary''s Student - gsnu200756


"Kim" wrote:

I have a very long list which I would like to sort (see first column example)
first by the last two digits and then the 3rd digit, so that the sorted list
would appear like column 2. I don't know where to start on this.

2297814 2179312
766961 2297814
2200735 2317528
2113780 2005233
2225041 2200735
604875 2225041
2103953 557042
557042 2272153
738066 2103953
2179312 766961
2272153 738066
2224766 2224766
2317528 604875
2126879 2126879
2005233 2113780
2260982 2260982