# Thread: sorting (2003)

1. ## 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. ## 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.

3. ## 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. ## 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>

5. ## 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
•