Tom’s Tutorials For Excel: Sorting The Unsortable

Tom’s Tutorials For Excel: Sorting The Unsortable

Sometimes you need to sort a list with varying hierarchy logic. For example, Picture #1 shows how “Section” is listed in column A with numbers in the same cell to indicate an order from 1 to 20.

If you try to sort that list in ascending order, the result is shown in the center pane of Picture #1 but what you really want is for the list be sorted as shown in the right pane of Picture #1.

Picture #1

In Picture #2, I am using a helper column by entering a formula in cell B1 that finds the first space in the value of cell A1, then returns the first number following that space. This formula is copied down to cell B10. It’s an array formula, applied to the cell by pressing Ctrl+Shift+Enter, not just Enter.

Note, I am breaking the formula into two lines just to accommodate all web browsers viewing this web page. In reality this formula is entered as a single line, as any formula would be.
=MID(A1,FIND(" ",A1)+1,MATCH
(FALSE,ISNUMBER(-MID(A1,{1;2;3;4;5;6;7;8}+FIND(" ",A1),1)),0)-1)+0

Picture #2

The next step is to sort the range in ascending order by column B, as shown in Picture #3.

Picture #3

After sorting, your list looks like that on the left of Picture #4.
The final step is to delete the helper formulas in column B to achieve your desired result as shown on the right of Picture #4.

Picture #4