sorting hyphenated numbers

Cobalt

New Member
Joined
Apr 3, 2002
Messages
3
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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top