Excel – How to sort list-style numbers in Excel

microsoft excelmicrosoft-excel-2010sorting

I have a column of "numbers" in Excel that I would like to sort as follows:

1.1
1.2
1.2.1
1.2.6
1.2.9
1.2.10
1.2.11
1.3

However, regardless if the cell's type is number or text, Excel sorts them as follows:

1.1
1.2
1.2.1
1.2.10
1.2.11
1.2.6
1.2.9
1.3

This could also be called "Natural sorting" — in a programming language like PHP this would be accomplished by the natsort function. But I cannot seem to find a way to sort in this manner inside Excel.

To help further provide some context: These numbers represent sections and sub-sections — not decimals, but rather as list items:

1. Section
    1. Sub-section
    2. Sub-section
        1.  Item
        6.  Item
        9.  Item
        10. Item
        11. Item
    3. Sub-section

Best Answer

You can make a table to split the values:

For each column, use the headings (row 1) and formulas (row 2+):

A1: text    A2: (your section numbers)
B1: dot1    B2: =FIND(".",A2,1)  
C1: dot2    C2: =IFERROR(FIND(".",A2,B2+1),LEN(A2)+1)  
D1: num1    D2: =VALUE(MID(A2,1,B2-1))  
E1: num2    E2: =IFERROR(VALUE(MID(A2,B2+1,C2-B2-1)),0)  
F1: num3    F2: =IFERROR(VALUE(MID(A2,C2+1,LEN(A2)-C2)),0)

This appears as:

      A      B      C      D      E      F

1   text   dot1   dot2   num1   num2   num3
2   1.1       2      4      1      1      0
3   1.1.3     2      4      1      1      3
4   2.10.7    2      5      2     10      7

Then you can do a custom sort by columns num1, num2 and num3.

Related Question