(This Formula Challenge originally appeared as Tip #85 in my weekly Google Sheets Tips newsletter, on 20 January 2020.
Sign up here so you don’t miss out on future Formula Challenges!
Start with a list of words in a single cell, separated by commas and not in alphabetical order, like so:
Your challenge is to create a single formula (i.e. in a single cell) that reorders this list into alphabetical order.
Use the SPLIT function to separate the comma-delimited string into separate cells.
(Split has two additional arguments and you have to be precise with your delimiter. In this simple example, we can omit the two additional arguments. See here for more info on the nuances of the SPLIT function.)
Use the TRANSPOSE function to change from row orientation to a column orientation, so that we can sort in Step 3.
Sort the data with the SORT function!
You don’t need to specify a column or direction, because we only have 1 column and we want ascending order, which is the default order. This keeps our formula brief.
Finally, join the column back together with the JOIN function, again using a comma as the delimiter.
There’s no need to use a second transpose because the JOIN function works with a column of data just as easily as a row of data!
I had over 150 responses to this formula challenge, and most came up with this same formula. It confirmed what I thought that there’s no shorter way to do it.
If you want to see how I used Apps Script to help me reply to these 150 emails, check out this article: Gmail Mail Merge For A Specific Label
Don’t Miss Future Formula Challenge!
There are more formula challenges in the pipeline.
If you want to get involved and up your Google Sheets game whilst you exercise the ‘ole grey matter, consider signing up to my Google Sheets Tips newsletter.