The result are the comma separated values in B5 sorted in alphabetical order. 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: 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: This array is returned directly to the SORT function. When we plug this back into our formula, we have: The SORT function then sorts the elements in the array and returns: Finally, the TEXTJOIN function re-concatenates all elements into a text string using a comma as a delimiter: 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

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.