Hi,
I'm looking for a very specific way to sort some values contained in a cell that are separated by spaces.
The values all starts with two digits followed by two letters.
Some contain more numbers following the letters but they are not important.
I'm looking to use a UDF to sort the values in an alphabetical order of the third and forth characters, then numerically ascending for any same letter groups.
e.g)
36AE12 36AF12 34AC32 35AA21 34AB21 35AE 35AF12 35AC234 35AB 37AF 38AF
expected outcome:
35AA21 34AB21 35AB 34AC32 35AC234 35AE 36AE12 35AF12 36AF12 37AF 38AF
The UDF function below is what I'm using today that needs to be changed. Below function sorts first two digits numerically from high to low, while keeping the alphanumeric sequence in an ascending manner. One problem I noticed with this is that it doesn't sort well when there are more than 10 substrings.
Thank you
I'm looking for a very specific way to sort some values contained in a cell that are separated by spaces.
The values all starts with two digits followed by two letters.
Some contain more numbers following the letters but they are not important.
I'm looking to use a UDF to sort the values in an alphabetical order of the third and forth characters, then numerically ascending for any same letter groups.
e.g)
36AE12 36AF12 34AC32 35AA21 34AB21 35AE 35AF12 35AC234 35AB 37AF 38AF
expected outcome:
35AA21 34AB21 35AB 34AC32 35AC234 35AE 36AE12 35AF12 36AF12 37AF 38AF
The UDF function below is what I'm using today that needs to be changed. Below function sorts first two digits numerically from high to low, while keeping the alphanumeric sequence in an ascending manner. One problem I noticed with this is that it doesn't sort well when there are more than 10 substrings.
VBA Code:
Function Chlwls(txt As String, Delim As String) As String
Dim Sp As Variant, Sp2 As Variant, Tmp As Variant
Dim i As Long, j As Long
Sp = Split(txt, Delim)
ReDim Sp2(UBound(Sp))
For i = 0 To UBound(Sp)
Sp2(i) = Left(Sp(i), 2) & Chr(UBound(Sp) - i + 65)
Next i
For i = 0 To UBound(Sp2) - 1
For j = i + 1 To UBound(Sp2)
If Sp2(j) > Sp2(i) Then
Tmp = Sp2(i): Sp2(i) = Sp2(j): Sp2(j) = Tmp
Tmp = Sp(i): Sp(i) = Sp(j): Sp(j) = Tmp
End If
Next j
Next i
Chlwls = Join(Sp, Delim)
End Function
Thank you