6

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

5 Answers 5

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.

1

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
0

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

http://youtu.be/74WdfqAB8mY

1
  • 4
    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, 2013 at 17:51
0

If you want to avoid complex formulas and trying to craft a solution that can handle n levels of indentation, you can do a little text editing to break apart the number components for a multi column sort solution.

There may even be a way to do this right in Excel, but I like text editors because I can accomplish what I want quickly.

  1. Copy the column contents that contains your multi-level list values
  2. Paste into text editor with decent replace functionality - personally, I love notepad++
  3. Replace level delimiter "." with tabs "\t"
  4. Select all (CTRL+A) and copy (CTRL+C)
  5. Click in a column to the right of your data in Excel and paste (CTRL+V)
  6. Select all the cells you just pasted and use Replace in Excel, replace blank cells with 0

enter image description here

You will have data in n columns for each number component, now you can sort on those columns.

enter image description here

0

Just faced the same issue...

if you want a quick solution (and are not fussed about the use of the "." then simply find all "." and replace with "_" - this will allow excel to sort in a hierarchy fashion.

the sort will almost work.. now all you need to do is add a trailing "underscore" to your data... so data with one character "1" becomes "1_" (to the number of characters in your data)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .