Formula Challenge #3: Alphabetize Comma-Separated Strings

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:

 

Find all the Formula Challenges archived here.

The Challenge

Start with a list of words in a single cell, separated by commas and not in alphabetical order, like so:

Epsilon,Alpha,Gamma,Delta,Beta

Formula Challenge 3

Your challenge is to create a single formula (i.e. in a single cell) that reorders this list into alphabetical order.

Step 1

Use the SPLIT function to separate the comma-delimited string into separate cells.

=SPLIT(A1,",")

(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.)

Step 2

Use the TRANSPOSE function to change from row orientation to a column orientation, so that we can sort in Step 3.

=TRANSPOSE(SPLIT(A1,","))

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.

=SORT(TRANSPOSE(SPLIT(A1,",")))

Step 4

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!

=JOIN(",",SORT(TRANSPOSE(SPLIT(A1,","))))

Bingo!

Formula Challenge 3 Solution

Community Solutions

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

6 thoughts on “Formula Challenge #3: Alphabetize Comma-Separated Strings”

  1. =JOIN(“,”,SORT(TRANSPOSE(SPLIT(A1,”,”))))
    SORT gives error message in my Excel 2010

  2. Does anyone know if the =JOIN(“,”,SORT(TRANSPOSE(SPLIT(A1,”,”)))) can be nested with an array formula?

Leave a Reply

Your email address will not be published. Required fields are marked *