How order a string in alphabetical order in a single row

Basically, I have a string with semicolon (;) separated in a Range ("H2"), example (Restrict; No-Resctric; Alpha; Cosmo; n..). This needs to be dynamic, meaning the string may contain more than four words in a single line.

'The expected results should be (Alpha; Cosmo; No-Restrict; Restrict; n..) in the Range ("H2")


  • Here's a VBA approach

    Sub sortCell(inp As Range)
        Dim arr
        arr = Strings.Split(inp.Value2, ";")
        Dim sList As Object
        Set sList = CreateObject("System.Collections.SortedList")
        Dim i As Long
        For i = 0 To UBound(arr)
            sList.Add CStr(arr(i)), Null
        Next i
        Dim result As String
        For i = 0 To UBound(arr)
            result = result & sList.getKey(i) & ";"
        Next i
        inp.Value2 = Strings.Left(result, Strings.Len(result) - 1)
    End Sub

    which you could call with, e.g.

    call sortCell(ActiveSheet.Range("H2"))