Sort using part of cell value

[KongUK]

Hi

I need to parse Right function to sort order key ie

Cell values per row are PART-***X, need to sort on last 5 characters (First part of value is not just PART)
Right(PART-1234, 5) = -1234

Can I fit the Right function into the sort key when using this approach instead using an additional calculated column?

ActiveSheet.Sort.SortFields.Add Key:=Range("E9:E" & lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlTextAsNumbers
    With ActiveSheet.Sort
        .SetRange (MyRange)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Thanks

[mikerickson]

Something like this should work. Using built in Excel features would require a helper column, this does not.

Sub test()
    Dim lastRow As Long
    Dim arrData As Variant
    Dim i As Long, j As Long, temp As Variant
    
    lastRow = 10: Rem adjust
    
    With Range("E9:E" & lastRow)
        arrData = .Value
        For i = 1 To lastRow - 10
            For j = i + 1 To lastRow - 9
                If LT(arrData(j, 1), arrData(i, 1)) Then
                    temp = arrData(i, 1)
                    arrData(i, 1) = arrData(j, 1)
                    arrData(j, 1) = temp
                End If
            Next j
        Next i
        .Value = arrData
    End With
End Sub

Function LT(a As String, b As String) As Boolean
    LT = (Right(a, 5) < Right(b, 5))
End Function

This could be modified to use a Quicksort and it could also be adjusted if you have more than one column for the sort.

[KongUK]

Thank you mikerickson, I will try it when back at work on Monday :-)