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

Have a look at [ask] a question with an [mcve] and edit your question accordingly please. — JvdV, Dec 04 '20 at 14:47
Sorry , i have edited it with adding pictures . is it fine ? — Ash Sooraj, Dec 04 '20 at 14:50
What you want is a "Natural Sort". See https://stackoverflow.com/questions/46180955/natural-or-human-sort-order for an example. That solution requires splitting the sort cell into multiple cells though. I'm not aware of a natural sort implementation for VBA. — Ryan, Dec 04 '20 at 14:53
Thanks for the link , but that is for SQL , i would like to know how to do it in excel , at least manually,i mean without splitting — Ash Sooraj, Dec 04 '20 at 14:55
It might be helpful to give more examples of the types of data that you have as inputs, including the more complex cases. I've implemented a Natural Sort before in Excel https://stackoverflow.com/a/62198108/3561813 that might help to get you started. While this code doesn't require you to split in the worksheet directly, the value will have to be split at some point to differentiate and designate numbers from letters. Let me know if that helps to get you started. — basodre, Dec 04 '20 at 15:03
@basodre i have updated the image , i hope it helps in better understanding of my issue — Ash Sooraj, Dec 04 '20 at 17:13
What about the suggestion @basodre posted above? Did you check that out? — Robert Harvey, Dec 04 '20 at 17:14
Sorry couldn't check it out,will check it out and let u know on monday. Anyway thanks @basodre for suggestion — Ash Sooraj, Dec 04 '20 at 17:36
@AshSooraj if you use the code from @basodre, you will need to adjust the `Comparer` function for your input. In his code it is `.Pattern = "([A-Za-z]+)(\d+)"` that will match `Name1234` but not your input. You will need something more complex since you have two embedded numbers, e.g. `A05-i[2][26]`. Or you can write formulas to split the input cell into multiple cells and use the other NaturalSort implementation. — Ryan, Dec 04 '20 at 18:36
@ashsooraj - posted an answer via tricky splitting several days ago; feel free to accept/upv. if helpful - c.f. ["Someone answers"](https://stackoverflow.com/help/someone-answers) :-) — T.M., Dec 09 '20 at 11:31

T.M. · Answer 1 · 2020-12-05T08:15:48.253

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

Sorting alphanumeric text in MS excel using VBA or manually

1 Answers1