Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer

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
share|improve this answer

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

http://youtu.be/74WdfqAB8mY

share|improve this answer
2  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.