VBA Function to Alphabetize Multiple Strings within a Cell

[ryancgarrett]

I have data that is consolidated into single cells. I'd like to alphabetize the strings within the cells they are in:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Original Data[/TD]
[TD]Alphabetized Data[/TD]
[/TR]
[TR]
[TD]Orange,Apple,Banana[/TD]
[TD]Apple,Banana,Orange[/TD]
[/TR]
[TR]
[TD]Grape,Apple,Grape,Banana[/TD]
[TD]Apple,Grape,Grape,Banana[/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD]Grape[/TD]
[/TR]
[TR]
[TD]Apple,Orange[/TD]
[TD]Apple,Orange[/TD]
[/TR]
</tbody>[/TABLE]

Unsure of the easiest way to handle the alphabetizing bit here.

 

[mikerickson]

This UDF should do what you want

Function SortDelimitedString(unSortedString As String, _
    Optional Descending As Boolean, Optional Delimiter As String = ",") As String

    Dim strLeft As String, strRight As String
    Dim strPivot As String, Words As Variant
    Dim i As Long
    
    Words = Split(unSortedString, Delimiter)
    strPivot = Words(0)
    
    For i = 1 To UBound(Words)
        If (Words(i) < strPivot) Xor Descending Then
            strLeft = strLeft & Delimiter & Words(i)
        Else
            strRight = strRight & Delimiter & Words(i)
        End If
    Next i
    strLeft = Mid(strLeft, Len(Delimiter) + 1)
    strRight = Mid(strRight, Len(Delimiter) + 1)
    If strLeft <> vbNullString Then
        strPivot = SortDelimitedString(strLeft, Descending, Delimiter) & Delimiter & strPivot
    End If
    If strRight <> vbNullString Then
        strPivot = strPivot & Delimiter & SortDelimitedString(strRight, Descending, Delimiter)
    End If
    SortDelimitedString = strPivot
End Function

 

[ryancgarrett]

Works like a charm!