Sort comma separated values

In this example the goal is to sort the comma separated values in column B in alphabetical order. In the latest version of Excel, you can solve this problem with a formula based on TEXTSPLIT, SORT, and TEXTJOIN. In earlier versions of Excel the problem is more complicated. See below for a couple of alternatives.

Current Excel

In the latest version of Excel, you can use a formula based on TEXTSPLIT, SORT, TEXTJOIN and (optionally) TRIM. In the example shown, the formula in cell D5, copied down, is:

=TEXTJOIN(",",TRUE,SORT(TRIM(TEXTSPLIT(B5,,","))))

Working from the inside out, the TEXTSPLIT function is configured to split the comma separated values in B5 into rows like this:

=TEXTSPLIT(B5,,",") // split into rows

Notice col_delimiter is left empty, and row_delimiter is provided as ",". We use row_delimiter instead of col_delimiter to save a little configuration with the SORT function coming up a bit later. The result from TEXTSPLIT is a vertical array like this:

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

This array is returned directly to the TRIM function:

TRIM({"C";"D";"A ";"F";"B";"E"}) // remove extra space

The TRIM function has just one job: remove extra space. This includes any leading or trailing spaces, as well as any extra space between values in normal text. In cell D5, TRIM strips the trailing space after "A" and returns the cleaned up array to the SORT function:

SORT({"C";"D";"A";"F";"B";"E"})

The SORT function then sorts the values in the array. By default, SORT will sort rows in ascending order. This is why we configured TEXTSPLIT to split values into rows: we can use this default behavior without any other configuration. Finally, the SORT function returns the sorted array directly to the TEXTJOIN function, which is configured to join values by with a comma:

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

Note delimiter is set to a comma (",") ignore_empty is set to TRUE, and text1 is delivered by the SORT function. 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 in Legacy Excel. One option is to use the Text to columns feature, then sort the values by column, and join them again with a formula that performs concatenation. This is obviously quite manual, but it can be done.

If you happen to have a version of Excel without TEXTSPLIT, but with SORT and TEXTJOIN, you can use a more complicated formula based on the FILTERXML function:

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

Basically, FILTLERXML is performing the role of TEXTSPLIT in the formula above. 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. See a more detailed explanation here. After that SORT and TEXTJOIN work as explained above. 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.