Sort range of alphanumeric values within a cell alphabetically (A to Z), then numerically (low to high)

[Chlwls808]

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.

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

 

[Akuini]

Try:

Function Chlwls808(tx As String) As String
Dim dar As Object, x, z
    Set dar = CreateObject("System.Collections.ArrayList")
    For Each x In Split(tx)
        z = Mid(x, 3, 2)
        dar.Add z & x
    Next
    dar.Sort
        For Each x In dar
            Chlwls808 = Trim(Chlwls808 & " " & Mid(x, 3))
        Next
End Function

 

[Peter_SSs]

Pretty similar, just a non-looping way to finish it off if you prefer.

Function SortEm(s As String) As String
  Dim AL As Object
  Dim itm As Variant
  
  Set AL = CreateObject("System.Collections.ArrayList")
  For Each itm In Split(s)
      AL.Add Mid(itm, 3, 2) & "# " & itm
  Next
  AL.Sort
  SortEm = Join(Filter(Split(Join(AL.ToArray)), "#", False))
End Function

 

[Chlwls808]

Wonderful!

Would it be a big change to also have a way to sort numerically high to low with all else being the same?

 

[Peter_SSs]

Would it be a big change to also have a way to sort numerically high to low with all else being the same?

If I have understood correctly, it is a very small change

Function SortIt(s As String) As String
  Dim AL As Object
  Dim itm As Variant
  
  Set AL = CreateObject("System.Collections.ArrayList")
  For Each itm In Split(s)
      AL.Add Mid(itm, 3, 2) & 999 - Left(itm, 2) & "# " & itm
  Next
  AL.Sort
  SortIt = Join(Filter(Split(Join(AL.ToArray)), "#", False))
End Function

Chlw;s808.xlsm
	A	B
1	36AE12 36AF12 34AC32 35AA21 34AB21 35AE 35AF12 35AC234 35AB 37AF 38AF	35AA21 35AB 34AB21 35AC234 34AC32 36AE12 35AE 38AF 37AF 36AF12 35AF12
Sheet2

Cell Formulas
Range	Formula
B1	B1	=SortIt(A1)

 

[Chlwls808]

God bless you @Peter_SSs
I hope you live long and be prosperous!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up, & the kind words.