unconventional sorting in excel

Question

i have a list like this:

G05
G03
F02
F06
G10
A03
A11
E10
E05
C11
C03
D03
A12
C12
F05
H03
C08
G02
D10
B12
C10
D11
C02
E11
E02
E03
H11
A08
D05
F04
A04
H07
D04
B07
F12
E04
B03
H05
C06
F08
C09
E08
G12
C04
B05
H09
A07
E09
C07
G07
G09
A06
D09
E07
E12
G04
A10
H02
G08
B06
B09
D06
F07
G06
A09
H06
D07
H04
H10
F10
B02
B10
F03
F11
D08
B11
B08
D12
H08
A05

i need it sorted in the following manner:

A03, B03, C03....A04, B04, C04.....A11, B11, C11........

the conventional sort can be done like this:

ActiveWorkbook.Worksheets("2871P1").Sort.SortFields.Add Key:=Range("D20:D99") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("2871P1").Sort
    .SetRange Range("D20:E99")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

however with this method, we are going to get A01, A02, A03 etc..., but this is not what i need

Answer 1

If this is a recurring situation you could move the sort to a macro so you would have much more control over the format.

The standard sorter in VBA won't give you access from within but my programmable LAselect add-in does. It allows you to intercept the records and also have regular expressions at your disposal if you really want a reusable tool.

A regular expression like "[0-9]+$" would return any number of digits from the end of a record and LAselect would sort them the way want. If the previous expression returns equal numbers then use "^[A-Za-z]*" to get at the textual parts.

You can find LASelect anywhere on the internet. Instructions of how to get regular expressions turned on in VBA and support for automatically adding them are only in the last release on www.liquorice-allsorts.com or else see http://www.regular-expressions.info/vb.html.

Answer 2

here's what i did. i first did a little string manipulation. instead of A03, i put 03A in the cell. then i sorted the entire thing. then i put it back to A03

Dim replace_string As String
Dim replace_number As String

For i = 20 To 98
    replace_string = Mid(Cells(i, 4), 1, 1)
    replace_number = Mid(Cells(i, 4), 2, 2)
    Cells(i, 4) = replace_number + replace_string
Next i


ActiveWorkbook.Worksheets("2871P1").Sort.SortFields.Add Key:=Range("D20:D99") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("2871P1").Sort
    .SetRange Range("D20:E99")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

For i = 20 To 98
    replace_string = Mid(Cells(i, 4), 3, 1)
    replace_number = Mid(Cells(i, 4), 1, 2)
    Cells(i, 4) = replace_string + replace_number
Next i

Answer 3

Split the column (using Text to Columns) so that you sort by the letters first, then the numeric column. Then recombine your columns.