VBAで以下のような文字列を並び替えたいときに意図しない変換がされてしまう。

例）

sort前	sort後	狙い
aa5	aa1	aa1
aa10	aa10	aa5
aa1	aa15	aa10
aa15	aa5	aa15

sort後の配列と狙いの配列に違いが出る理由は、
aaの後の数値が文字列として扱われているためにおこる現象となっている。

これを改善するため、文字と数値を別のセル分割して入力し、
並び替えを行うことで解消した。

このマクロは長い文字列で数値と文字列が複雑に混ざっているものでも
sortできるようになっている。

sample.bas

Sub Sample()
    Dim A As Variant, i As Long, j As Long
    Dim MaxColumn As Integer
    Dim MaxRow As Integer
    MaxRow = 1
    
    MaxColumn = WorksheetFunction.CountA(Range("A:A"))
    For i = 1 To MaxColumn
        A = Splits(Cells(i, 1))
        For j = 0 To UBound(A)
            Cells(i, j + 2) = A(j)
        Next j
        If MaxRow < UBound(A) + 2 Then MaxRow = UBound(A) + 2
    Next i
    
On Error GoTo ErrorHandler
    Range(Cells(1, 1), Cells(MaxColumn, MaxRow)).Select
    With Range(Cells(1, 1), Cells(MaxColumn, MaxRow)).SpecialCells(xlCellTypeBlanks)
        .Value = "'"
    End With
    
ErrorHandler:
   
    For i = MaxRow To 2 Step -1
        Cells(1, i).Sort key1:=Cells(1, i), order1:=xlAscending
    Next

End Sub

Function Splits(A As String)
    Dim i As Long, flag As Boolean, B As String, cnt As Long, c() As String
    flag = IsNumeric(Left(A, 1))
    For i = 1 To Len(A)
        If flag <> IsNumeric(Mid(A, i, 1)) Then
            ReDim Preserve c(cnt)
            c(cnt) = B
            B = ""
            cnt = cnt + 1
            flag = Not flag
        End If
        B = B & Mid(A, i, 1)
    Next i
    ReDim Preserve c(cnt)
    c(cnt) = B
    Splits = c
End Function

行っていること

A列のセルを数値と文字列で分割してB列以降に表示
分割したセルを一番右からsortする

一番右から順にsortすると、
右側ほどsortの上書きがされるため、重要度が低くなるという役割を持つ。