Sorting based on letter, and then on number

[snb]

I'd suggest to use VBA:

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion.Columns(1)

  With GetObject("new:{00000535-0000-0010-8000-00AA006D2EA4}")
    .Fields.Append "S", 129, 3
    .Fields.Append "N", 5
    .Open
        
    For j = 1 To UBound(sn)
      .AddNew
      .Fields("N") = StrReverse(Val(StrReverse(sn(j, 1))))
      .Fields("S") = Replace(sn(j, 1), .Fields("N"), "")
      .Update
    Next
    .Sort = "S,N"
      
    MsgBox Replace(Replace(.getstring, vbTab, ""), " ", "")
  End With
End Sub

or

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion.Columns(1)

  With GetObject("new:{00000535-0000-0010-8000-00AA006D2EA4}")
    .Fields.Append "S", 129, 3
    .Fields.Append "N", 5
    .Open
      
    For j = 1 To UBound(sn)
      .AddNew
      .Fields("N") = StrReverse(Val(StrReverse(sn(j, 1))))
      .Fields("S") = Replace(sn(j, 1), .Fields("N"), "")
      .Update
    Next
    .Sort = "S,N"
      
    sp = .getrows
    For j = 0 To UBound(sp, 2)
      sn(j + 1, 1) = Trim(sp(0, j)) & sp(1, j)
    Next
      
    Sheet1.Cells(1).CurrentRegion.Columns(1) = sn
  End With
End Sub