Summary

To sort comma separated values in a cell, you can use an unlikely formula based on the FILTERXML function, with help from SORT, TEXTJOIN, and SUBSTITUTE. In the example shown, the formula in cell C5, copied down, is:

=TEXTJOIN(",",1,SORT(FILTERXML("<x><y>"&SUBSTITUTE(B5,",","</y><y>")&"</y></x>","//y")))

The result are the comma separated values in B5 sorted in alphabetical order.

Generic formula

=TEXTJOIN(",",1,SORT(FILTERXML("<x><y>"&SUBSTITUTE(A1,",","</y><y>")&"</y></x>","//y")))

Explanation 

In this example the goal is to sort the comma separated values in column B in alphabetical order. In Excel 365, the SORT function provides a straightforward way to sort values in individual cells (or in an array), but the main challenge here is that all values are in one cell.

Working from the inside out, the first step is to extract the values into an array that can be sorted with the SORT function. This is done with the FILTERXML and the SUBSTITUTE functions in this snippet:

FILTERXML("<x><y>"&SUBSTITUTE(B5,",","</y><y>")&"</y></x>","//y")

This formula is explained in detail here. In brief, we use SUBSTITUTE to convert the text values into a very simple XML format, then use FILTERXML to extract the values into an array. The result is a vertical array like this:

{"C";"D";"A";"F";"B";"E"}

This array is returned directly to the SORT function. When we plug this back into our formula, we have:

=TEXTJOIN(",",1,SORT({"C";"D";"A";"F";"B";"E"}))

The SORT function then sorts the elements in the array and returns:

{"A";"B";"C";"D";"E";"F"}

Finally, the TEXTJOIN function re-concatenates all elements into a text string using a comma as a delimiter:

=TEXTJOIN(",",1,{"A";"B";"C";"D";"E";"F"}) // returns "A,B,C,D,E,F"

The final result is the text string "A,B,C,D,E,F".

When using FILTERXML, keep the following in mind:

1. White space is ignored, a bit like using the TRIM function. You can add space characters later with TEXTJOIN if needed.

2. Numbers end up in General number format. You could use the TEXT function to process the values after sorting to convert numeric values into a specific format.

3. A double comma will throw a #VALUE error. You could catch this error with IFERROR and remap to a default value if needed.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.