Search code examples
excelvbastringrangealphabetical

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")

Solution

  • 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"))