On 2002-04-04 20:32, Cobalt wrote:
I have a spreadsheet listing stamps. One of the columns is the Scott number, an ID number issued to the stamp. It could be 1 or 66a or 2354. My problem is that the stamps are recorded in groups, say 64-67a or 1-12 or 79-108 or 122 (group of one). How do I get these to sort so that the right order is achieved? I want 1-12, 13-17, 127, not 1-12, 127, 13-17. Can this be done?
Thanks!
Consider the following sample in A1:A10.
{1;"66a";2354;"67a";"1-12";"64-67a";"79-108";122;"117a";"c235"}
In B1 enter:
=SUBSTITUTE(A1,"-","")
Give a double click on the fill handle of the cell B1 (in order to copy down the formula).
In C1 enter:
=SUBSTITUTE(B1,IF(ISNUMBER(RIGHT(B1)+0),SUBSTITUTE(B1,RIGHT(B1,SUMPRODUCT((LEN(B1)-LEN(SUBSTITUTE(B1,{0,1,2,3,4,5,6,7,8,9},""))))),""),SUBSTITUTE(B1,LEFT(B1,SUMPRODUCT((LEN(B1)-LEN(SUBSTITUTE(B1,{0,1,2,3,4,5,6,7,8,9},""))))),"")),"")
Give a double click on the fill handle of the cell C1.
Now sort the area in A to C on column C.
This is what I get as result:
{1;"1-12";"117a";122;"c235";2354;"64-67a";"66a";"67a";"79-108"}
After saving the formulas for future use, delete columns B and C.
Aladin
This message was edited by Aladin Akyurek on 2002-04-05 00:03
This message was edited by Aladin Akyurek on 2002-04-05 00:04