Sorting alphanumeric text as alphabetically VBA

Question

I have a excel Sheel Which Have alphanumeric text, as Can be seen in Picture:

I Want To sort All Rows with Custom Sort List as ("Wireless", "Landline", "VOIP"),

vba Code Working But its sort Data as numerical while i want to Sort data as alphabetically And Ignore Numbers Please Tell Me Solution.

Sub SortIndividualR()
'Updateby Extendoffice
    Dim xRg As Range, yRg As Range, vCustom_Sort As Variant, rr As Long
    vCustom_Sort = Array("Wireless", "Landline", "VOIP", Chr(42))
    Application.AddCustomList ListArray:=vCustom_Sort
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set xRg = Selection
    If xRg.Count = 1 Then
        MsgBox "Select multiple cells!", vbExclamation, "Kutools for Excel"
        Exit Sub
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    Application.ScreenUpdating = False
    For Each yRg In xRg.Rows
         yRg.NumberFormat = "@"
        yRg.Sort Key1:=yRg.Cells(1, 1), Order1:=xlAscending, _
       Orientation:=xlSortRows, Header:=xlYes, MatchCase:=False, _
                    OrderCustom:=Application.CustomListCount + 1

        
    Next yRg
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    Application.ScreenUpdating = True
End Sub

user_9758194 · Answer

Assuming:

One of these three options in each cell is present;
The format of each cell is as per your sample data;
You don't need VBA per se;
Access to ms365.

Formula in F1:

=LET(X,TOROW(A1:D1,1),SORTBY(X,FIND(MID(X,18,1),"WLV"),,X,))

I choose to 1st sort by your custom range and 2nd by the actual numeric looking string.

If you don't have acces to TOROW(), you can change to FILTER():

=LET(X,FILTER(A1:D1,A1:D1<>""),SORTBY(X,FIND(MID(X,18,1),"WLV"),,X,))

Sorting alphanumeric text as alphabetically VBA

1 Answers