Ah, so you have to format on a cell-by-cell basis.
Makes sense.
That won't do it by itself. what you need to do is "extract" the suffix letter from the number, add a value for the suffix, and then sort by the combination. That's why I said it's clunky.
So let's do an example: Column A has the mixed up room numbers:
7
5
5c
5a
4
4a
1
3
2a
2
If you just type that into Excel it will auto format the numbers with suffixes as text, and the other as numbers. Then in Cell B1 you type =SORT(A1:A10) (change the cell numbers to your actual cell range) and auto fill it down. You will then get this:
7 1
5 2
5c 3
5a 4
4 5
4a 7
1 2a
3 4a
2a 5a
2 5c
Sorted ascending, with numbers first and text second. In cell C1 you type =IF(ISTEXT(B1:B10),VALUE(LEFT(B1:B10,LEN(B1:B10)-1)),B1:B10) and autofill it down (again, replace the cell arrays with the appropriate numbers). This will extract the numbers from the cells with suffixes and asign everything a numerical value. OUr example now looks like:
7 1 1
5 2 2
5c 3 3
5a 4 4
4 5 5
4a 7 7
1 2a 2
3 4a 4
2a 5a 5
2 5c 5
now that everything is a numerical value, in cell D1 type =RANK.EQ(C1,C$1#,1) and it'll rank all the cells in Columns C according to their position in the array. Spreadsheet now looks like:
7 1 1 1
5 2 2 2
5c 3 3 4
5a 4 4 5
4 5 5 7
4a 7 7 10
1 2a 2 2
3 4a 4 5
2a 5a 5 7
2 5c 5 7
Now that everything is ranked, in Cell E1 type =SORTBY(B1#,D1:D10,1) and it will bring the values from column "B" over, but rank them by the values in column "D". You will now have:
7 1 1 1 1
5 2 2 2 2
5c 3 3 4 2a
5a 4 4 5 3
4 5 5 7 4
4a 7 7 10 4a
1 2a 2 2 5
3 4a 4 5 5a
2a 5a 5 7 5c
2 5c 5 7 7
Highlight and Hide columns B,C, and D and you have your list sorted. Obviously this is a little clunky for 10 values, but if you have several hundred rooms, it may be worth it. it's also a pain to add more rooms after the sort. It's not super handy, but that's how you do it.