Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sort numbers and letters (excel 2000)

    Hello all, I have a column in excel that i want to sort but it wont work, for example i have the digits 5, 21, 11aa, 1, 8, 4b. when i sort them i get 1, 5, 8, 21, 11aa, 4b.
    but i wont that it is 1, 4b, 5, 8, 11aa, 21. is this possible?? i have looked by format cells but that won't work....

    Gerwin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sort numbers and letters (excel 2000)

    Gerwin,

    Others will probably come up with a more clever solution, but you could use array formulas to split the values into a numeric and an alphabetic part.
    For instance, if your values start in A1, this formula in B1 extracts the numeric part:
    =1*LEFT(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW($1:$9), 1)),0)-1)
    And this formula in C1 extracts the alphabetic part:
    =MID(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW($1:$9),1)) ,0),255)
    Both must be entered as array formulas, i.e. confirm with Ctrl+Shift+Enter instead of Enter. You can fill down the formulas as far as needed, then sort on the second and third column. See the attached workbook.

    Note: array formulas inspired by Bob Umlas.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sort numbers and letters (excel 2000)

    One additional column to convert a numeric to text should do the trick.

    You can use = Text(A1,"0"), where A1 has a value with or without a following letter.

    I have hijacked Hans's example to show what I mean. See attcahed.

    Andrew

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: sort numbers and letters (excel 2000)

    Another method is a macro approach similar to the index sorting in <post#=347173>post 347173</post#>

    Select the region and it will sort based on the initial "value part" and then the rest. The macro reads the selection (can be multiple columns if you want to sort an "datatable", then adds 2 columns to it: the initial numbers from the first range and the then the "rest". It sorts based on the number first and if equal the "rest"

    Steve

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort numbers and letters (excel 2000)

    Thanks for all the codes........

    Gerwin

Posting Permissions

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