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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials For Excel: Sorting The Unsortable
  1. Sanad says:

    Super Great! Thanks a lot Tom (Happy New Year!)
    This sorting has given me headaches for over 2 decades now.
    Your solution is amazing, and has no VBA.
    I follow you on Twitter and learning a lot.
    Thanks again.

    • Tom Urtis says:

      Thanks for the kind words. I’ll be adding more content during 2017. Let me know if there’s something you’d like to see how to solve and I’ll try to put up an example. Happy New Year!

Leave a Reply

Your email address will not be published. Required fields are marked *