Sorting value with two decimal points in excel

Question

I'm trying to sort an excel table by a certain column, these values are in the form 1.2.3 and I'm trying to find a way to sort them in ascending order. The built in excel sort function seems to do a good job however when numbers get into double digits it doesn't do exactly what I want.

Initial Value	Actual Outcome	Expected Outcome
1.1.1	1.1.1	1.1.1
1.1.2	1.1.11	1.1.2
1.11.1	1.1.2	1.1.11
1.2.1	1.11.1	1.2.1
1.1.11	1.2.1	1.11.1

The way I managed to do it is to split the column into three columns and sort them from right to left. However the issue with this is that it requires making new columns and then removing them which isn't something I can really ask the users of this sheet to do. There are also columns with data on both sides of this column in the sheet.

Is there a way to do the sorting in a single excel function or using VBA? Ideally it would be a single click to sort the table if that's possible.

Answer 1

Still working my way around Lambda, but this formula seems to work:

=BYROW(SORT(TEXTSPLIT(TEXTJOIN(",",FALSE,$A$2:$A$6),".",",")*1,{1,2,3}),LAMBDA(DataArray,TEXTJOIN(".",FALSE,DataArray)))  

It doesn't use a helper column, but also doesn't sort the original data in place.

Thought I'd better add an explanation as I'm up against Ron. Need to up my game. :)

• TEXTJOIN(",",FALSE,A2:A6) combines the whole list into a single list, separated by a comma.

• TEXTSPLIT(<textjoin>,".",",")*1 splits it into a spilled range with each number in its own column. The *1 converts the text values to numeric values.

• =SORT(<textsplit>,{1,2,3}) sorts the columns.
  You could update the {1,2,3} array to SEQUENCE(,LEN(A2)-LEN(SUBSTITUTE(A2,".","") if you're going to add more columns - all columns must have same number of . though.

• All the above makes up the DataArray used in the Lambda.

• LAMBDA(DataArray,TEXTJOIN(".",FALSE,DataArray) joins the data back together.

• BYROW sticks the data back together row by row.

Answer 2

If you have Excel 365, you can use this formula to return a sorted output:

=SORTBY(sortRange,BYROW(sortRange,LAMBDA(arr,TEXTJOIN(".",TRUE,TEXT(TEXTSPLIT(arr,"."),"000")))))

• TEXTSPLIT splits your dot separated string
• TEXT then pads each segment with 0's.
  • *If your values have more than three digits, change the padding
• We then join things back together with TEXTJOIN
• and SORTBY sorts the original array by the padded one we constructed.
• the BYROW function allows us to enter this as a single formula which will have its results spill down, instead of doing a line by line activity.

If you have a larger table than just a single column, change the first argument of SORTBY to include the entire table, but leave SortRange only referring to the column you wish to base the sort on in the BYROW function.

Answer 3

I would advise you to use a helper column, and in there you put the values, after having replaced the dots with a multiplication by one hundred.

I beg your pardon?

I mean that you create a UDF (a User-Defined Function) which performs the following conversion from your type of entry to an integer number:

1.2.3    => 1002003 ((1 * 100) + 2) * 100 + 3
1.1.1    => 1001001 ((1 * 100) + 1) * 100 + 1
1.11.1   => 1011001 ((1 * 100) +11) * 100 + 1
1.2      => 1002000 ((1 * 100) + 2) * 100 + 0 (in case '1.2' equals '1.2.0')
...

That UDF can be created, using some basic string manipulation functions, combined with string-to-integer conversion functions (like CInt()).

Have fun

Answer 4

Please, test the next function. It uses arrays (one of them being a helper array, built in the known way to sort chapters) and a Bubble sort an array based on another one and should be very fast. It (now) will return in the next column "B:B", if the range to be sorted in in "A:A"), only to see the output. If it is on your test, you should only adapt the last code line of the testing Sub:

Function SortChapters(rng As Range)
    Dim arr(), helperArr(), arrSplit() As String, i As Long, j As Long
    arr = rng.Value2: helperArr = arr 'helperArr will be used as bases for sorting both arrays
    
    'format each cell/array element in a way to be correctly sorted:
    For i = 1 To UBound(helperArr)
        arrSplit = Split(helperArr(i, 1), ".")
        For j = LBound(arrSplit) To UBound(arrSplit)
            arrSplit(j) = Format(CLng(arrSplit(j)), String(3, "0"))
        Next j
        helperArr(i, 1) = Join(arrSplit, ".")  ' re-join the array element by "."
    Next i
   
   'sort arr based on helperArr:
    BubbleSortTwo2D helperArr, arr             'sort arr, based on helperArr
    
    SortChapters = arr
End Function

Private Sub BubbleSortTwo2D(arrRef, arr) 'it sorts arr, based on arrRef!
    Dim i As Long, j As Long, temp, temp1
    
    For i = LBound(arrRef) To UBound(arrRef) - 1
        For j = i + 1 To UBound(arrRef)
            If arrRef(i, 1) > arrRef(j, 1) Then
                temp = arrRef(i, 1): arrRef(i, 1) = arrRef(j, 1)
                temp1 = arr(i, 1): arr(i, 1) = arr(j, 1)
                arrRef(j, 1) = temp: arr(j, 1) = temp1
            End If
        Next j
    Next i
End Sub

It can be tested with the next Sub:

Sub testSortChapters()
  Dim sh As Worksheet, rng As Range, lastR As Long, arr
  
   Set sh = ActiveSheet 'use here the necessary sheet
   lastR = sh.Range("A" & sh.rows.count).End(xlUp).row 'last row on A:A
   Set rng = Range("A1:A" & lastR)
   arr = SortChapters(rng)
   rng.Offset(, 1).Value2 = arr
End Sub

As I wrote above, if the code return is convenient, the code line:

   rng.Offset(, 1).Value2 = arr

should become:

  rng.Value2 = arr

And the code return will be placed in place of the processed range...

The above code should work in any Excel version. If Excel 365, the function can be used as UDF (User Defined Function) and called it from a cell. Placing the next formula in "B1" will return in "B:B":

  =SortChapters(A2:A6)

This solution is able to deal with chapters and subchapters up to three digits (999), for each...

Please, send some feedback after testing the code.