Sort range of values within a cell

[Chlwls808]

Hi,
A bit complex question..

Because Excel don't allow sorting values inside one cell, I'm trying to use a UDF to handle the sorting. The below module is suppose to help me do it in alphabetical order of a given list of items contained in a cell.

Function SortWithinCell(CelltoSort As Range, DelimitingCharacter As String, IncludeSpaces As Boolean) As String
CelltoSortString = WorksheetFunction.Substitute(CelltoSort.Value, " ", "")
MyArray = Split(CelltoSortString, DelimitingCharacter)
    For N = 0 To UBound(MyArray)
        For M = 1 To UBound(MyArray)
            
                If MyArray(M) < MyArray(M - 1) Then
                    TempValue = MyArray(M)
                    MyArray(M) = MyArray(M - 1)
                    MyArray(M - 1) = TempValue
                End If

        Next M
    Next N
For N = 0 To UBound(MyArray)
    SortWithinCell = SortWithinCell & MyArray(N) & DelimitingCharacter
Next N
SortWithinCell = Left(SortWithinCell, Len(SortWithinCell) - 1)
If IncludeSpaces = True Then SortWithinCell = WorksheetFunction.Substitute(SortWithinCell, ",", ", ")
End Function

But suppose you have a list that looks like this:

33AA21 33AB21 34AC32 36AE12 36AF12

The main goal is to sort it numerically based on first two digit - Highest to lowest, then alphabetically AA to ZZ.. the next numbers aren't relevant.

Appreciate any feedback on the above.

Thanks!

 

[Fluff]

Did you remove the old function?

 

[Chlwls808]

Did you remove the old function?

I pasted on top of the old one and saved it.

 

[JEC]

Tried my post already?

 

[Chlwls808]

You can try this as well

Function JEC(cell As String, delimiter As String) As String
  With CreateObject("system.collections.arraylist")
    For Each it In Split(cell, delimiter)
      .Add it
    Next
      .Sort
      JEC = Join(.toarray)
  End With
End Function

Tried my post already?

Hi Jec, thanks for your post and willing to help out.

I did, but weirdly, it's doing the same thing with your function, too. Now I think this has to do with my program or something...

 

[Fluff]

In that case check that macros are enabled.

 

[JEC]

I bet your function is in a worksheet module.

Create a new module en paste the code there, remove it from the worksheet module

 

[Fluff]

I bet your function is in a worksheet module.

Maybe you haven't read the thread.

Yes, it's in standard (code) module.

 

[JEC]

Didn't read that one indeed. Then try copy your compleet workbook and try again.

 

[Chlwls808]

Fluff, JEC,

Thanks for your input. After removing the module and reinserting, it finally works.

Would it be too difficult to tweak it a little to sort numbers highest to lowest, while keeping the Alphabetical order as is?

So instead of

rather the output to

36AE12 36AF12 34AC32 33AA21 33AB21

 

[JEC]

Function jec(cell As String, delimiter As String) As String
  With CreateObject("system.collections.arraylist")
    For Each it In Split(cell, delimiter)
      .Add it
    Next
      .Sort
      .Reverse
      jec = Join(.toarray)
  End With
End Function