Explanation

At the core, this formula uses the MID function to extract each character of a text string in reverse order. The starting character is given as a list of numbers in descending order hard-coded as array constant:

MID(B5,{10,9,8,7,6,5,4,3,2,1},1)

The text argument comes B5, and 1 is specified for the number of characters to extract.

With the string “ABCD” in B5, the output from MID is an array that looks like this:

{"","","","","","","D","C","B","A"}

This array is fed into the TEXTJOIN function as the text1 argument, with delimiter set to an empty string (""), and ignore blank set to TRUE (entered as 1):

=TEXTJOIN("",1,{"","","","","","","D","C","B","A"})

The TEXTJOIN function concatenates each element in the array together, ignoring blanks, and returns the final result, “DCBA”

Dynamic array

The array constant in the above example will only support string up to 10 characters. To use a dynamic array that scales to the right size, you can use a more complicated formula like this

=TEXTJOIN("",1,MID(B5,ABS(ROW(INDIRECT("1:"&LEN(B5)))-(LEN(B5)+1)),1))

More information about generating an array of numbers here .

Dynamic array with SEQUENCE function

Excel 365 supports dynamic array formulas . In Excel 365, the SEQUENCE function can generate dynamic number arrays in one step. With SEQUENCE, the formula above can be simplified to:

=TEXTJOIN("",1,MID(B5,SEQUENCE(LEN(B5),,LEN(B5),-1),1))

Inside SEQUENCE, the LEN function returns the count of characters in B5, 4. This result is used both for the rows argument and the start argument, with -1 provided for the step argument:

SEQUENCE(4,,4,-1) // returns {4;3;2;1}

and this array is delivered to the MID function as the start_num argument.

Explanation

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

FILTERXML is only available in Excel for Windows, not Excel for Mac, or Excel Online.