How to sort list-style numbers in Excel?

Question

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

Answer 1

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.

Answer 2

I have had good results by inserting a User Defined Function. The UDF returns a value that represents the total of the section number in millions, the subsection number in thousands, the item number in units, the subitem (if any) in thousandths, sub-sub items in millionths, and so on. For example,

1.2.3 would return 1,002,003 while

1.51.5.5 returns 1,051,005.005 - and these numbers can then be used for sorting.

I find this to be less cumbersome than using in-worksheet string functions, or sorting on multiple columns.

The UDF is as follows:

Function LList(stInVal As String) As Double

Dim iPower          As Integer
Dim vSplit          As Variant
Dim i               As Long

iPower = 6

vSplit = Split(stInVal, ".", -1)

For i = 0 To UBound(vSplit)
    LList = LList + CInt(vSplit(i)) * 10 ^ (iPower - 3 * i)
Next i

End Function