AlphaNumeric Sort

P

Pallav Saxena

Hi,
I am trying to sort a worksheet on the basis of the Lot#.
The lot# has values like
1
2
1s
3
2s
4s
4f
10
5
2
5t


I tried this but no luck...

Public Sub SortLotNumber()
'Sort properly
Dim lr As Integer
Dim J As Integer
Dim length As Integer
Dim CellVal As String

Worksheets("Item Data").Activate
lr = GetRealLastRow("Item Data")
For J = 3 To lr
Worksheets("Item Data").Cells(J, 1).Value = "0" &
Worksheets("Item Data").Cells(J, 1).Value & " "
Next J

Rows("3:" & lr).Sort Key1:=Range("A3"),
Order1:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

For J = 3 To lr
length = Len(Worksheets("Item Data").Cells(J,
1).Value)
CellVal = Worksheets("Item Data").Cells(J, 1).Value
Worksheets("Item Data").Cells(J, 1).Value = Left
(Right(CellVal, length - 1), length - 2)
Next J
End Sub

How to do it using vba.. please help....

Regards
Pallav Saxena
 
A

Arvi Laanemets

Hi

You can do it easily using the ordinary sort feature in Excel, when you have
your values in format like
001
002
001s
etc. (how much leading zeros, depends of your data, my example is for case
the numeric part doesn't exceed 999)

To convert the existing numbers, you can use next formula (this one is for
converting value in A2):
=TEXT((MID(A2,1,1) & IF(ISNUMBER(VALUE(MID(A2,2,1))),MID(A2,2,1),"") &
IF(ISNUMBER(VALUE(MID(A2,3,1)));MID(A2,3,1),""))*1,"000")&SUBSTITUTE(A2,MID(
A2,1,1)&IF(ISNUMBER(VALUE(MID(A2,2,1))),MID(A2,2,1),"")&IF(ISNUMBER(VALUE(MI
D(A2,3,1))),MID(A2,3,1),""),"")


Arvi Laanemets


T
 
A

Arvi Laanemets

I missed one ";" when editing the formula. The right formula is:

=TEXT((MID(A2,1,1) & IF(ISNUMBER(VALUE(MID(A2,2,1))),MID(A2,2,1),"")
&IF(ISNUMBER(VALUE(MID(A2,3,1))),MID(A2,3,1),""))*1,"000")&SUBSTITUTE(A2,MID
(A2,1,1)&IF(ISNUMBER(VALUE(MID(A2,2,1))),MID(A2,2,1),"")&IF(ISNUMBER(VALUE(M
ID(A2,3,1))),MID(A2,3,1),""),"")


Arvi Laanemets
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top