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]

Do you mean that you want it to sort a string that is separated by spaces, as opposed to (say) commas?

 

[Chlwls808]

Do you mean that you want it to sort a string that is separated by spaces, as opposed to (say) commas?

Yes, it's separated by space. And the sort needs to be numerically (highest to lowest), tiebreaker being the alphabetical (A to Z). Thank for responding.

 

[Fluff]

In that case you can use this mod to your code

Function SortWithinCell(CelltoSort As Range, DelimitingCharacter As String) As String
MyArray = Split(CelltoSort, 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)
End Function

 

[Chlwls808]

Thanks again for the mod! But when I try it, I get invalid name error. Does this happen to you by any chance?

=SortWithinCell(A1," ")

 

[Fluff]

Does this happen to you by any chance?

Nope, is the code in a standard module, or a sheet module?

That said, you don't need the function, as that cell is already sorted.

 

[Chlwls808]

Nope, is the code in a standard module, or a sheet module?

That said, you don't need the function, as that cell is already sorted.

Yes, it's in standard (code) module. What if I need the numbers to sort highest first then to lowest (while keeping the Alphabetical order as is)?

 

[Fluff]

Are you still getting the #Name error?

 

[JEC]

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

 

[Chlwls808]

Are you still getting the #Name error?

Yes, I am. I'm not sure why though.. Excel recognizes the =SortWithinCell function as I'm typing it in.. so I know it's understood.. but when it's fully applied, it says the function has invalid name for some reason.