In earlier versions of Excel the problem is more complicated.
See below for a couple of alternatives.
We userow_delimiterinstead ofcol_delimiterto save a little configuration with the SORT function coming up a bit later.
This includes any leading or trailing spaces, as well as any extra space between values in normal text.
By default, SORT will sortrowsinascendingorder.
TEXTJOIN joins each value in the array separated with a comma.
The result is a single text string of comma separated values, sorted in alphabetical order.
Legacy Excel
This is not an easy problem to solve inLegacy Excel.
This is obviously quite manual, but it can be done.
After that SORT and TEXTJOIN work as explained above.
TEXTSPLIT can split text into rows or columns.
Values can be sorted by one or more columns.
SORT returns a dynamic array of results.
TEXTJOIN Function
The Excel TEXTJOIN functionconcatenatesmultiple values together with or without a delimiter.
FILTERXML Function
The Excel FILTERXML function returns specific data from XML text using the specified XPath expression.
