# How to sort list-style numbers in Excel?

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
``````
-

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.

-

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
``````
-

Doug explains this in detail. I hope it makes sense!

http://youtu.be/74WdfqAB8mY

-
Please read the SU FAQ, www.superuser.com/faq. Just providing links as answers is discouraged. Can you provide some of the details from the video in your post? This would make it able to stand on its own if the video ever goes away. –  slm Mar 6 '13 at 17:51