Let’s try something really cool.
We have the list from earlier of application skills in a combined, unsorted manner, and we wish to have them in a combined, sorted manner.
There isn’t a single function solution to produce this result, but we can achieve the goal in three simple steps:
- Split the text into separate cells using the TEXTSPLIT
- Sort the separated results using the SORT
- Recombine the individual elements back into a single cell using the TEXTJOIN
Step 1 – Split the text
We’ll start by utilizing our new favorite function, TEXTSPLIT, to separate the skills into cells.
NOTE: I’ve filled the formula down to the adjacent rows to apply the formula to the remainder of the list. I’ll be doing this for the following steps as well.
Because some (not all) of the comma delimiters have spaces following them, we’ll slip in a little TRIM function to remove the extraneous spaces.
Step 2 – Sort the Separated Skills
We can easily sort the skills by nesting the TRIM/TEXTSPLIT functions inside a SORT function.
=SORT(TRIM(TEXTSPLIT(A2, ",") ), , , TRUE )
In the SORT function, we had to skip the 2nd and 3rd arguments ([sort_index] and [sort_order]) to be able to define the 4th argument, [by_col]. The TRUE tells the SORT function to sort the data by columns as opposed to by rows which is the default behavior.
Step 3 – Combine the Results into a Single Cell
The final step is to use a TEXTJOIN function to combine the separate cells into a single cell.
=TEXTJOIN(", ", TRUE, SORT(TRIM(TEXTSPLIT(A2, ",") ), , , TRUE ) )
The TEXTJOIN function uses a comma-space as the delimiter, and the TRUE tells TEXTJOIN to ignore any empty cells in our data. As we didn’t have any empty cells, TRUE or FALSE would yield the same result.