Forum Discussion
Excel Sorting Weird
I've noticed recently that Excel will sort my rooms not exactly in order. I'm sure it has something to do with how we label rooms in our system. Is there anything I can do to make it sort correctly? Even if I have to go in and redo the rooms? Below is a short example that has cropped up recently. The sheet was sorted by building (not shown) and room (below, smallest to largest). I have no issues with how it sorted the buildings, but as you can see it doesn't like room 103. It has sorted 103 at the top but all the sub-103's (103-a, e, g, h) were sorted below 102-Storage.
Rooms as Sorted:
103
103
103
103
103
103
107
107
107
109
111
111
202
214
215
215
218
218
226
232
232
102-STORAGE
102-STORAGE
103-a
103-a
103-a
103-a
103-a
103-e
103-e
103-e
103-g
103-h
As I write this out is the hyphen what is messing it up? Is there any workarounds that can be recommended? We have some offices that are sub single offices marked 10A however if there are multiple desks in one office space we mark it as 11-A,11-B,11-C for example. So I can't just remove the hyphen unless there is a better workaround.
103 is a number but 103-A is text. Excel sorts number before text.
You might format the cells as text before entering the room numbers, or prefix the values with an apostrophe '.
But you'd also have to enter a room number such as 10 or 10A or 10-A as 010, 010A and 010-A.
