Sorting Alphanumeric Data

**drgogo** · 02-08-2010, 09:42 AM

Ok, before any one shoots me down, i have looked around the forum and the internet for a answer, Im im half way there.

I have a column in my worksheet which contains alphanumeric data, I also have a Custom menu option to sort the worksheet but 3 columns.

No i know if i have a column contains the following

1
1
2a
3b
3c
2

and i have set the cells to text then it would sort as follows

1
1
2
2a
3c
3b

Which is cool but i have a colum looking like this

3
3
3
8
8a
8b
65a
65
71
71

and when sorted looks like this

3
3
3
65
65a
71
71
8
8a
8b

this is because excel is using the first digit, but i need it to sort as whole numbers so it would look like below

3
3
3
8
8a
8a
65
65a
71
71

Any ideas, I need this to be included in teh VBA code i have for sorting

Sub SortSpecial()
Dim FirstRow As Long, EndRow As Long, LastRow As Long
ActiveSheet.Protect Password:="gideon", UserInterfaceOnly:=True
LastRow = Range("B" & Rows.Count).End(xlUp).Row
FirstRow = Range("D1").End(xlDown).Row



    Do
        If FirstRow > LastRow Then Exit Sub
        
        EndRow = Range("D" & FirstRow).End(xlDown).Row
        Range("B" & FirstRow, "P" & EndRow).Sort Key1:=Range("D" & FirstRow), Order1:=xlAscending, _
            Key2:=Range("E" & FirstRow), Order2:=xlAscending, _
            Key3:=Range("H" & FirstRow), Order3:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
            DataOption2:=xlSortNormal
        FirstRow = Range("D" & EndRow).End(xlDown).Row
    Loop
    
 
    
End Sub

Thanks G

**DonkeyOte** · 02-08-2010, 09:59 AM

Simplest solution is perhaps to use an adjacent to normalise the numerics... eg if we assume your values are in A1:A10 then:

B1: =LOOKUP(9.99E+307,--MID(A1,1,ROW(A$1:INDEX(A:A,LEN(A1)))))
copied down

You can then sort by B and then A.

**drgogo** · 02-08-2010, 10:25 AM

so i would have to set up another column and then sort that column to sort the main column, does this column have to be adjacent or can it be futher down the sheet.

Would it cope with blank cells as not all cells int he column has data ?

**DonkeyOte** · 02-08-2010, 10:28 AM

It might be easier to post a sample with before/after sheets so people can better visualise exactly what it is you want to achieve
(ie are blanks to be ignored or pushed to the top / bottom etc...)

**shg** · 02-08-2010, 10:44 AM

Maybe a UDF?

Function PadNums(sInp As String, Optional ByVal iLen As Long = 1) As String
    ' shg 2003-1115

    ' Expands numbers in a string to iLen characters for sorting; e.g.,
    '   PadNums("13A1U3", 2)    = "13A01A03"
    '   PadNums("1.2.3.15", 3)  = "001.002.003.015"
    
    ' Numbers are not shortened below their minimal representation:
    '   PadNums("1.123.2.3", 2) = "01.123.02.03"
    
    ' Returns unpadded values if iLen omitted
    '   PadNums("01.123.02.03") = "1.123.2.3"
    
    ' All non-numeric characters are returned as-is
    
    Dim sFmt    As String
    Dim i       As Long
    Dim iNum    As Long
    Dim sChr    As String
    Dim bNum    As Boolean
    
    If iLen < 1 Then iLen = 1
    If iLen > 15 Then iLen = 15
    sFmt = String(iLen, "0")

    For i = 1 To Len(sInp)
        sChr = Mid(sInp, i, 1)
        If sChr Like "#" Then
            bNum = True
            iNum = iNum * 10 + CInt(sChr)
        Else
            If bNum Then
                PadNums = PadNums & Format(iNum, sFmt)
                iNum = 0
                bNum = False
            End If
            PadNums = PadNums & sChr
        End If
    Next
    If bNum Then PadNums = PadNums & Format(iNum, sFmt)
End Function

In B1 and copy down, =PadNums(A1, 2), then sort by column B.

**drgogo** · 02-15-2010, 10:30 AM

I have posted an example of what im trying todo.

The example shows a before sort and after sort, the column higlighted in flesh colour or pink indicates the columns containing alphanumeric data

The sort code i use at the moment is as follows with column D being the alphanumeric column

Sub SortSpecial()
Dim FirstRow As Long, EndRow As Long, LastRow As Long
ActiveSheet.Protect Password:="gideon", UserInterfaceOnly:=True
LastRow = Range("B" & Rows.Count).End(xlUp).Row
FirstRow = Range("D1").End(xlDown).Row



    Do
        If FirstRow > LastRow Then Exit Sub
        
        EndRow = Range("D" & FirstRow).End(xlDown).Row
        Range("B" & FirstRow, "P" & EndRow).Sort Key1:=Range("D" & FirstRow), Order1:=xlAscending, _
            Key2:=Range("E" & FirstRow), Order2:=xlAscending, _
            Key3:=Range("H" & FirstRow), Order3:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
            DataOption2:=xlSortNormal
        FirstRow = Range("D" & EndRow).End(xlDown).Row
    Loop
    
 
    
End Sub

As you will see from the small example, there are blank cells in the columns and there is no limit to the amount of data that can be contained on the sheet. All other columns that are sorted by teh VB code work as they should

Hope you can help

G

**shg** · 02-16-2010, 08:59 PM

You can use the function I provided in col G (or any other convenient column).

To get the blank rows to remain where they are, you need to put something in the sort column that keeps them there.

**drgogo** · 02-23-2010, 09:21 AM

Hmmm, thanks for the help

Have tried your UDF as previoull mentioned, but it did not sort at all, it produced the same effect as a normal sort, Dunno if i have done anything wrong, but it did not produce the sort i requirer

G