| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sorting using a second word in a cell | Excel Discussion (Misc queries) | |||
| sorting a concatenated cell | Excel Discussion (Misc queries) | |||
| Sub-cell List Sorting | Excel Discussion (Misc queries) | |||
| Sorting data at end of cell | Excel Discussion (Misc queries) | |||
| link cell in 1 wk-sht to another & keep it regardless of sorting | Excel Worksheet Functions | |||
Hybrid Mode
