sorting problem

[rjtaylor]

I am having a problem on sorting
I have a worksheet with around 36000 rows
I am sorting on three columns the first column is mostly Alphabet characters however I have around 800 entries with Numbers
My problem is some of these numbers have leading zeros. Also some of these numbers are only numbers while others start with numbers and then have text following the number
The leading Zeros seems to be the biggest problem I am having

If I select treat text that looks like a number i get the leading zeros to work
however any number/text combo, comes after the numbers so

01 Colibri
01 Festival
01 Isatis
will come after all numbers

And

1001 Pingouin 1
1002 Pingouin 2
101 Arava

is not correct

The following is if I select treat numbers and text that looks like a number separately

1
01
01 Colibri
01 Festival
01 Isatis
02
03
1
1
1 Fermer
10
10
10
10 Albertan
10 Ultimate Competitor
10 Ultimate Winner
10 Voyager
100
100
100 Centennial
100 Commander 100
100 Commander 100
100 Darter Commander
100 King Air
100 Lark Commander
100 Sunbird
1000
1001 Pingouin 1
1002 Pingouin 2
101
101 Arava

Any ideas?
preferably via VBA

 

[ranman256]

i created another field ,SORTVAL, and used this custom function to get a real #...

Public Function Cvt2Num(ByVal pvVal)
Dim i As Integer
i = InStr(pvVal, " ")
If i = 0 Then
  Cvt2Num = CInt(pvVal)
Else
  Cvt2Num = CInt(Left(pvVal, i - 1))
End If
End Function

 

[rjtaylor]

Thanks Ranman but i am confused on how this is used

 

[rjtaylor]

i came up with a solution from Ranmans example here is what i used

Sub ttySortSetUp()
'first get all cells that start with numbers and followed by a space including leading 0
For Each cell In Range("C2:C11000")
    If cell.Value <> "" Then
        a = Cvt2Num_3(cell.Value)
        cell.Offset(0, 40) = a
    End If
Next
'Now get all other cells that start with a number
For Each cell In Range("C2:C11000")
    If cell.Value <> "" Then
        If cell.Offset(0, 40) = "" Then
            a = Cvt2Num_4(cell.Value)
            cell.Offset(0, 40) = a
        End If
    End If
Next
End Sub

Public Function Cvt2Num_3(ByVal pvVal)
Dim i As Integer
On Error Resume Next
i = InStr(pvVal, " ")
If i = 0 Then
  Cvt2Num_3 = CInt(pvVal)
Else
  Cvt2Num_3 = CInt(Left(pvVal, i - 1))
End If
On Error GoTo 0
End Function

Public Function Cvt2Num_4(ByVal pvVal)
Dim i As Integer
If IsNumeric(Left(pvVal, 1)) Then
    For j = 1 To Len(pvVal)
        If Not IsNumeric(Mid(pvVal, j, 1)) Then
            Cvt2Num_4 = CInt(Left(pvVal, j - 1))
            Exit For
        End If
    Next
End If

End Function

Thanks for the help