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

Chlwls808

Board Regular
Joined
Jun 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
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.

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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Try:
VBA Code:
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
 
Upvote 0
Pretty similar, just a non-looping way to finish it off if you prefer.

VBA Code:
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
 
Upvote 0
Solution
Wonderful!

Would it be a big change to also have a way to sort numerically high to low with all else being the same?
 
Upvote 0
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

VBA Code:
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
AB
136AE12 36AF12 34AC32 35AA21 34AB21 35AE 35AF12 35AC234 35AB 37AF 38AF35AA21 35AB 34AB21 35AC234 34AC32 36AE12 35AE 38AF 37AF 36AF12 35AF12
Sheet2
Cell Formulas
RangeFormula
B1B1=SortIt(A1)
 
Upvote 0
You're welcome. Thanks for the follow-up, & the kind words. :)
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top