sort alphanumeric data
 

I have data with mix number of digits and I like to sort them. Somehow, the
normal sorting process give me a not so desired result. Can anyone help?
A10
B100
A2
AA1
AD200
A1
The result I am looking for is:
A1
A2
A10
B2
B100
AD200

sort alphanumeric data
 

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)
InsertModule
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

--
Kind regards,

Niek Otten

"yip" wrote in message
...
I have data with mix number of digits and I like to sort them. Somehow, the
normal sorting process give me a not so desired result. Can anyone help?
A10
B100
A2
AA1
AD200
A1
The result I am looking for is:
A1
A2
A10
B2
B100
AD200

sort alphanumeric data
 

47 should have been 48 and 58 should have been 57 in both cases

--
Kind regards,

Niek Otten


"Niek Otten" wrote in message
...
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)
InsertModule
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

--
Kind regards,

Niek Otten

"yip" wrote in message
...
I have data with mix number of digits and I like to sort them. Somehow,
the
normal sorting process give me a not so desired result. Can anyone help?
A10
B100
A2
AA1
AD200
A1
The result I am looking for is:
A1
A2
A10
B2
B100
AD200