Exceljet

Quick, clean, and to the point

Sort comma separated values

Excel formula: Sort comma separated values
Generic formula 
=TEXTJOIN(",",1,SORT(FILTERXML("<x><y>"&SUBSTITUTE(A1,",","</y><y>")&"</y></x>","//y")))
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.

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 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.

Dynamic Array Formulas are available in Excel 365 only.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.