Sorting based on letter, and then on number

[Paul_Hossler]

This seems to match what you wanted

You might have to change the way the logic is used since I used a driver sub and the range to sort is hard coded

It basically takes the cells in the sort range, reformats in a format to sort, sorts, and then changes the cell back to the original format

Option Explicit


Sub drv()
    
    Call LetterNumberSort(Range("A2:A10"))


End Sub




' composed of 1 to 3 letters, and then a number, with the length of this number ranging from 1 to 5 digits.
Sub LetterNumberSort(r As Range)
    Dim c As Range
    Dim i As Long
    Dim s As String
    
    With r
    
        For Each c In .Cells
            c.Value = pvtFormatToSort(c.Value)
        Next




        With .Parent.Sort
            .SortFields.Clear
            .SortFields.Add Key:=r, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange r
            .Header = xlNo
            .MatchCase = True
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        For Each c In .Cells
            c.Value = pvtFormatToDisplay(c.Value)
        Next
    End With
End Sub




Private Function pvtFormatToSort(s As String) As String
    Dim s1 As String, s2 As String, s3 As String
    
    s1 = Trim(UCase(s))
    
    If s1 Like "[A-Za-z]#*" Then
        s2 = Left(s1, 1) & "00"
        s3 = Right("00000" & Right(s1, Len(s1) - 1), 5)
        
    ElseIf s1 Like "[A-Za-z][A-Za-z]#*" Then
        s2 = Left(s1, 2) & "0"
        s3 = Right("00000" & Right(s1, Len(s1) - 2), 5)
        
    
    ElseIf s1 Like "[A-Za-z][A-Za-z][A-Za-z]#*" Then
        s2 = Left(s1, 3)
        s3 = Right("00000" & Right(s1, Len(s1) - 3), 5)
    End If
            
                
     pvtFormatToSort = s2 & s3
End Function


Private Function pvtFormatToDisplay(s As String) As String
    Dim s1 As String, s2 As String
    Dim i As Long
    
    If s Like "[A-Za-z]#*" Then
        i = 2
    ElseIf s Like "[A-Za-z][A-Za-z]#*" Then
        i = 3
    ElseIf s Like "[A-Za-z][A-Za-z][A-Za-z]#*" Then
        i = 4
    End If
        
    s1 = Left(s, i - 1)
    
    Do While Mid(s, i, 1) = 0
        i = i + 1
    Loop
    
    s2 = Right(s, Len(s) - i + 1)
            
     pvtFormatToDisplay = s1 & s2
End Function