sorting hyphenated numbers

[Cobalt]

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!

[Anne Troy]

If I were you, I'd separate my From and To values into two columns. Just make sure you've got a blank column to the right. Then select your whole column and hit Data-Text to Columns. Tell is to use the hyphen as the delimiter.

Then you should be able to easily sort by the first column. If it won't sort for you, it could be that your numbers are still being seen as text, and you may have to do the *Times One* fix to make them be seen as numbers.

The Times One Fix
Put a 1 in any cell that is not formatted as text. Copy the cell. Select your numbers, hit Edit-Paste special-Multiply.



_________________
TheWordExpert

[ This Message was edited by: Dreamboat on 2002-04-04 20:43 ]

[Yogi Anand]

Hi Cobalt:
Continuing with the line of thinking of Dreambot's suggestion, you could insert a column, then use the following formula

=SUBSTITUTE(E2,"-",".")
Drag this formula down

so
1-12
13-17
127
will look like
1.12
13.17
127
Then EDIT|COPY and PASTE|SPECIAVALUES to convert these to truly as numeric values.
Then sort on this column.

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

[Cobalt]

Thanks! That seems to work for the pure numbers, but the 117a & c235 numbers still don't sort right. I would tell the owner that it just can't be done in a spreadsheet, but he swears one of his competition has a s/s that sorts his hyphenated Scott numbers.

[Yogi Anand]

On 2002-04-04 21:10, Cobalt wrote:
Thanks! That seems to work for the pure numbers, but the 117a & c235 numbers still don't sort right. I would tell the owner that it just can't be done in a spreadsheet, but he swears one of his competition has a s/s that sorts his hyphenated Scott numbers.

Hi Cobalt:
A generic solution in that case will be to convert your original entries to a dummy colum such as:
001-12
013-17
127
using a formula like =REPT("0",4-FIND("-",B19,1))&B19
to be the sort column.

I am sure others can suggest even still different ways of handling this!

[Aladin Akyurek]

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 ]

[Cobalt]

Thanks for the great responses! Unfortunately, I haven't gotten anything that works. With Aladin's formulas, I start with 127, 1-12, 64a, c129, 15-17 and after, I get 1-12, 127, c129, 15-17, 64a. I'm about ready to tell the owner that it just can't be done.

[Aladin Akyurek]

On 2002-04-08 20:14, Cobalt wrote:
Thanks for the great responses! Unfortunately, I haven't gotten anything that works. With Aladin's formulas, I start with 127, 1-12, 64a, c129, 15-17 and after, I get 1-12, 127, c129, 15-17, 64a. I'm about ready to tell the owner that it just can't be done.

The result should have been:

{"1-12";
"15-17";
"64a";
127;
"c129"}

If so,

in D1 enter:

=IF(ISNUMBER(SEARCH("-",A1)),C1/(10^(LEN(A1)-SEARCH("-",A1))),C1+0)

and sort on column D.

Is this what is desired?




[ This Message was edited by: Aladin Akyurek on 2002-04-08 20:50 ]