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.
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!
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!