+ Reply to Thread
Results 1 to 3 of 3

Alpha numeric sort

  1. #1
    Registered User
    Join Date
    03-23-2009
    Location
    los angeles
    MS-Off Ver
    Excel 2003
    Posts
    25

    Unhappy Alpha numeric sort

    I am trying to sort alpha numeric data like so -

    PA1
    PA2
    PB1
    PB2
    PB10

    When I sort, the alphabet part is sorted right, but the number part is is not what I want -

    PB10 is right below PB1-

    PB1
    PB10
    PB2....

    Any work around?

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,958

    Re: Alpha numeric sort

    You would need to insert leading zeros to your numbers, like this:

    PA01
    PA02
    PB01
    PB02
    PB10

    If you have a lot of these then you could use a formula like this:

    =IF(ISNUMBER(RIGHT(A1,2)+0),A1,LEFT(A1,LEN(A1)-1)&TEXT(RIGHT(A1),"00"))

    assuming the list starts in A1, and then use the column with the formula in as the sort field, and then you could remove that column.

    Hope this helps.

    Pete

  3. #3
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Alpha numeric sort

    With your data in Column A, In B1 enter:

    =LEFT(A1,2) and copy down

    In C1 enter:

    =--MID(A1,3,9999) and copy down

    Then sort all three columns, first by column B and then by column C
    Gary's Student

+ 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