Results 1 to 5 of 5

Thread: sorting (2003)

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sorting (2003)

    I need to sort classroom numbers in ascending order. The classrooms are frequently numbered with a letter first followed by the actual number. Is there some magical sorting trick I need to learn in order to force excel to sort these not only by letter but by number CORRECTLY - not all the rooms that start with B1 (B1, B10, B11, etc) ?

    Urgently need someone's help - thanks again.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting (2003)

    You will need to change the way you enter the classroom numbers. Instead of B1, enter B01 and they should sort in the correct order.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting (2003)

    You could also create a custom list and use that to sort correctly...of course, to do this, you need a list in the proper order to begin with....

    One other idea would be to create two other columns to separate the data before and after the "-".
    For the first column, assuming rooms numbered A-Z, =left(b1,1)
    For the second column, =MID(B1,FIND("-",B1)+1,4)*1
    Then sort by your letter and number columns.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting (2003)

    I just did a little more thinking about this. Here is something else you can try. If the room numbers are not in column A, the following formula will need to be modified to change all references to A1 to be the cell where the first room number is located. Select an empty column and enter the following formula in cell A1. Copy the formula down as far as the room numbers go. Sort on this column.

    <pre>=IF(AND(ISTEXT(A1),LEN(A1)=2),LEFT(A1,1)&"0"& RIGHT(A1,1),A1)
    </pre>

    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting (2003)

    Perfect again - thanks.

Posting Permissions

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