Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: sorting hyphenated numbers

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •