How order a string in alphabetical order in a single row

Question

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

Answer 1

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

Answer 2

Since there is an Excel Tag and assume there is no Excel Constraints this can be accomplished using TEXTSPLIT()

---

• Formula used in cell B1

=SORT(TRIM(TEXTSPLIT(A1,,";",1)))

---

Or, within the cell:

---

• Formula used in cell B1

=TEXTJOIN("; ",,SORT(TRIM(TEXTSPLIT(A1,,";",1))))

---

Caveat: Since the output is asked to produce within a cell hence using TEXTJOIN() there is character limitations i.e. if the string exceeds 32767 characters (cell limit), TEXTJOIN() returns the #VALUE! error. Here is the MSFT documentation. Hence it is better to use VBA or split by rows/columns which ever suits with your data.

---