Sort range of values within a cell

Chlwls808

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

VBA Code:
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!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Do you mean that you want it to sort a string that is separated by spaces, as opposed to (say) commas?
 
Upvote 0
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.
 
Upvote 0
In that case you can use this mod to your code
VBA 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
 
Upvote 0
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," ")

1630079272679.png
 
Upvote 0
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.
 
Upvote 0
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)?
 
Upvote 0
Are you still getting the #Name error?
 
Upvote 0
You can try this as well

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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