Sorting alphanumeric text in MS excel using VBA or manually

Question

I have alphanumeric text in a column in Excel. I need to sort them in alphabetic order and also numerically.

The only way I could do this is by extract number to another column and then sort it, but it is not feasible for me as there same number may occur multiple times, there maybe multiple brackets and i need them to sort in alphabetic order too. I would like to know the VBA code also to automate this.

As you can see in the below image with A to Z sorting, "A05 [1][21]" came between "A05 [1][2]" & "A05 [1][3]", but I want it to be numerical order as shown in expected result..

Answer 1

Natural Sort via splitting

This approach

• [1] assigns data to a "vertical" 2-dim array with two columns,
• [2] enters a 2nd criteria column with numeric bracket values via Split() function and executes a bubble sort and
• [3] writes the sorted data back to the original range.

Sub ExampleCall()
    '[0]define data range (starting at 2nd row in column A:A)
    With Sheet1                              ' << change to project's sheet Code(Name)
        Dim lastrow As Long: lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Dim rng As Range:    Set rng = .Range("A2:A" & lastrow)
    End With
    '[1]assign data to datafield array
    Dim data: data = rng.Resize(, 2)
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    '[2]calculate sort criteria and sort data
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    FillSortCriteria data                   ' << run procedure FillSortCriteria
    NaturalSort data                        ' << run procedure NaturalSort

    '[3]write results
    'rng.Offset(, 1) = data                 ' (optional insertion into next column)
    rng = data                              ' overwrite range
End Sub

Help procedure FillSortCriteria

Sub FillSortCriteria(arr)
    'Purpose: calculate criteria in 2nd column of 2-dim array
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Dim tokens: tokens = Split(arr(i, 1) & "[[", "[")
        arr(i, 2) = Left(tokens(0) & String(10, " "), 10) & _
                    Format(Val(tokens(1)), "000") & "." & _
                    Format(Val(tokens(2)), "000")
    Next i
End Sub

Further hints

Splitting a string like "A05-i [1][21]" by delimiter "[" results in a zero-based array where the first token, i.e. token(0) equals "A05-i", the 2nd "1]" and the 3rd "21]". The Val() function converts the bracket items to a numeric value ignoring non-numeric characters to the right.

These tokens can be joined to a sortable criteria in the second column of the passed array; as the arr argument has been passed ByReference by default thus referring to the data array in the calling procedure, all entries change immediately the referring data entries.

Help procedure NaturalSort (modified Bubblesort)

Sub NaturalSort(arr)
'Purpose: natural sort of 2nd and 3rd token (numbers in brackets)
'Note:    assumes "vertical" 2-dim array holding criteria in 2nd column
    Dim cnt As Long, nxt As Long, temp, temp2
    For cnt = LBound(arr) To UBound(arr) - 1
        For nxt = cnt + 1 To UBound(arr)
            If arr(cnt, 2) > arr(nxt, 2) Then
                temp = arr(cnt, 1):        temp2 = arr(cnt, 2)
                arr(cnt, 1) = arr(nxt, 1): arr(cnt, 2) = arr(nxt, 2)
                arr(nxt, 1) = temp:        arr(nxt, 2) = temp2
            End If
        Next nxt
    Next cnt
End Sub