Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort by the code

    I'm sorting the spreadsheet the column ordered by date but I want to order by the code, R1 thru R5000, and the sorting does not appeared as I meant. For example, I want to sort the number R1, R2, R3......R5000, but it sorted as R1, R10, R100, R1000, then R1001, R1002,..., then R101, R1010, R1011,..... I think it's easy if I can separate letter and number but I don't know how to separate. Can anyone help?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Soochun,

    Welcome to the Lounge as a new poster!

    You'll need to insert a "Helper Column" next to the row to be sorted.
    Enter this formula in the first row of the inserted column: =VALUE(RIGHT(A1,LEN(A1)-1)).
    Of course you'll change the A1's to the appropriate column.
    Copy the formula down the column.
    Sort on the new column.
    If no longer needed delete the helper column.
    Before -------------------------------- After
    Sort on Col A.JPGSort on Col B.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Dec 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your help. It separates the number and letter but sorting the number is still not in order. I want to sort in order of 1,2,3,4....5000, but it's order is; 1, 10, 100, 1000, 1001-1009, 101, 1010-1019, 102, 1020-1029, 103, 1030-1039, .... How can I sort the number in order?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Soochun,

    Did you copy the formula exactly?
    If you look at the two graphics you'll see it does sort correctly. If it is sorting the way you state it's most likely because you didn't include the VALUE portion of the formula or you're sorting on the column with the R's vs the helper column w/just the numbers.

    I just ran a test of R1-R5000 and it works correctly.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Dec 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes. It worked very well. After the separation with letter and number, I just sort directly. But When I paste value then sort, it worked perfectly fine. Thank you so much.

Posting Permissions

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