+ Reply to Thread
Results 1 to 8 of 8

Excel 2007 : Sorting numerically and alphabetically ignoring preceding text

  1. #1
    Registered User
    Join Date
    06-27-2012
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Sorting numerically and alphabetically ignoring preceding text

    I am needing to sort the list below numerically first and then alphabetically ignoring everything before the number. The problem I have run into with all functions is there is no consistency in the length of the number or the text following it. Any help would be GREATLY appreciated.


    PNW 027.625 Nor
    PNW 133.1 Mun v. 2
    PNW 248 Smi
    PNW 266 Dry
    PNW 289.6 Bee
    PNW 305.22 Swe
    PNW 305.244 Herric
    077.3 Pre
    178.3 Sul
    179.3 Wiehl
    180.7 Armstr
    182.7 Chapma
    200.7 Tenzer
    270 Alv
    279.1 Holtha
    279.20973 Edelma
    O 077.3 Pre
    O 178.3 Sul
    O 179.3 Wiehl
    O 180.7 Armstr
    O 182.7 Chapma
    O 200.7 Tenzer
    O 270 Alv

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sorting numerically and alphabetically ignoring preceding text

    based on your sample, how would the sorted list look like? (perhaps a sample file with a before and after please?)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Sorting numerically and alphabetically ignoring preceding text

    Save and re-import your file as a comma separated values file.
    As delimiter, choose space
    Now you have a 4 column separated list.
    Move the block of cells that have numbers in A and letters in B 1 to the right (the entire block)
    Go to Sort, choose custom sort and sort first by column B then by column C

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Sorting numerically and alphabetically ignoring preceding text

    Hi ShaylaDawn,

    I've been working on your problem - I assume that the alpha sort is for the entries that look like names??
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  5. #5
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Sorting numerically and alphabetically ignoring preceding text

    Try this ...
    With your data in A2 down
    In B2
    Please Login or Register  to view this content.
    in C2
    Please Login or Register  to view this content.
    Drag/Fill both Down

    Select A2:C24, Sort on Column B ascending, then by Column C ascending.

    The attached is unsorted.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Sorting numerically and alphabetically ignoring preceding text

    Hi ShaylaDawn,

    This is a UDT and Load-Array-Sort routine - copy paste it to the top of a standard module:

    Please Login or Register  to view this content.
    The output looks like this:

    270 Alv
    O 270 Alv
    180.7 Armstr
    O 180.7 Armstr
    PNW 289.6 Bee
    182.7 Chapma
    O 182.7 Chapma
    PNW 266 Dry
    279.20973 Edelma
    PNW 305.244 Herric
    279.1 Holtha
    PNW 133.1 Mun v. 2
    PNW 027.625 Nor
    077.3 Pre
    O 077.3 Pre
    PNW 248 Smi
    178.3 Sul
    O 178.3 Sul
    PNW 305.22 Swe
    200.7 Tenzer
    O 200.7 Tenzer
    179.3 Wiehl
    O 179.3 Wiehl

  7. #7
    Registered User
    Join Date
    06-27-2012
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Sorting numerically and alphabetically ignoring preceding text

    Thanks so much. I have this problem solved and now on to the next problem which involves merging several lists based on a common column. Thanks again.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Sorting numerically and alphabetically ignoring preceding text

    You're welcome.

+ 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