I think your example wasn't exactly right' look at the B2 and AA1 entries.
But I assume you want to sort like the Excel row and column identifiers; I
also assume there will be two alpha characters at most
Go to the VB Editor (ALT+F11)
Insert>Module
Paste these two functions in the code window:
Function PartOne(a As String) As String
Dim i As Long
For i = 1 To Len(a)
If Asc(Mid(a, i, 1)) < 47 Or Asc(Mid(a, i, 1)) > 58 Then
PartOne = PartOne + Mid(a, i, 1)
End If
Next i
If Len(PartOne) = 1 Then PartOne = " " + PartOne
End Function
Function PartTwo(a As String)
Dim i As Long
For i = 1 To Len(a)
If Asc(Mid(a, i, 1)) >= 47 And Asc(Mid(a, i, 1)) <= 58 Then
PartTwo = PartTwo + Mid(a, i, 1)
End If
Next i
PartTwo = CDbl(PartTwo)
End Function
If your data is in column a, put this in B1:
=PartOne(A1)
and in C1:
=PartTwo(A1)
Copy both down as far as needed.
Now sort on column B and C
