+ Reply to Thread
Results 1 to 3 of 3

Sorting a list of letters & numbers by a number mid-sequence

  1. #1
    Registered User
    Join Date
    10-13-2011
    Location
    Perth, Oz
    MS-Off Ver
    Excel 2003
    Posts
    2

    Sorting a list of letters & numbers by a number mid-sequence

    Hello! I hope you can help me.
    I currently have a list of numbers and letters which I need to sort in excel. An example of the list is as follows:
    SNR-WGP/APJ 81#10A
    SNR-WGP/APJ81#21+ST-BYF/SNR81#187
    SNR-WGP/APJ 81#22A
    SNR-WGP/APJ 81#254/1
    SNR-WGP/APJ 81#2/1
    SNR-WGP/APJ81#20+ST-BYF/SNR81#188
    SNR-WGP/APJ 81#254/6
    SNR-WGP/APJ 81#255/1
    SNR-WGP/APJ 81#254/G
    SNR-WGP/APJ81#3+ST-BYF/SNR81#194/11

    I need to sort this list by the number after the first # in the sequence. Then by the numbers/letters after this number. Then if it is a sequence with a + in it, then with the last number in order too.

    So for this example, I'd like it in the following order:
    SNR-WGP/APJ 81#2/1
    SNR-WGP/APJ81#3+ST-BYF/SNR81#194/11
    SNR-WGP/APJ 81#10A
    SNR-WGP/APJ81#20+ST-BYF/SNR81#188
    SNR-WGP/APJ81#21+ST-BYF/SNR81#187
    SNR-WGP/APJ 81#22A
    SNR-WGP/APJ 81#254/1
    SNR-WGP/APJ 81#254/6
    SNR-WGP/APJ 81#254/G
    SNR-WGP/APJ 81#255/1

    As you can see, there are different sequence lengths, plus letters and numbers at the end as well as combinations of APJ 81# and APJ81#. Complicated much?

    Please help! It's doing my head in

    PS I'm a newb, I'm sorry. I am using Excel 2010 on Windows7. :/

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,464

    Re: Sorting a list of letters & numbers by a number mid-sequence

    I'd be inclined to use Text to Columns using the # as a delimiter. Extract to a new (Helper) column ignoring the first column (the data before the #) and then sort on the Helper column.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    10-13-2011
    Location
    Perth, Oz
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Sorting a list of letters & numbers by a number mid-sequence

    Thank you for your reply. Unfortunately, if I do that, it sorts within the sequence types but won't put the entire list in order of the number after the first #. :o/

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1