Marie Mosley

Posted on

# Alphabetize a Comma-Separated List in Google Sheets

I subscribe to Ben Collins' "Google Sheets Tips" newsletter, and I learn so much from it!

This week's newsletter included a challenge: alphabetize a comma-separated list of words with a single formula.

As in, go from this in cell A2: Epsilon,Alpha,Gamma,Delta,Beta
To this in cell B2: Alpha,Beta,Delta,Epsilon,Gamma

Ben dropped a hint that the `TRANSPOSE` function would help solve the challenge.

Up to now, I've only used `TRANSPOSE` for one thing: filling a column with the contents of a row, or vice-versa, usually when referencing data from one sheet on another. So, I was a little puzzled about how it would help alphabetize values in a single cell โ and how that could work in a one-liner ๐ค

I took some time to pick apart the challenge and I was able to crack it in four steps with the help of `TRANSPOSE` and three other functions.

You can see the formula broken down in Google Sheets. Each part of the formula has its own sheet in there so you can see how this works step-by-step.

### Step One: `SPLIT`

When I see a cell with comma-separated values in it, I know I'm probably gonna have to do a `SPLIT` ๐

The `SPLIT` function breaks text in a cell into pieces based on a delimiter character, and puts each piece of the text into a separate cell in the same row.

In this case, the text in the cell is a series of comma-separated words, so the delimiter character is the comma.

I split the text in the cell into individual words with this function:

`=SPLIT(A1,",")`

Ok, cool, that breaks the comma-separated string into individual words. But how do I sort them into alphabetical order?

Ideally I'd use `SORT`, which can alphabetically sort words in a column.

But...these words aren't in a column. They're in a row.

Ah ha!๐กThis is where `TRANSPOSE` pitches in to help.

### Step Two: `TRANSPOSE`

After the `SPLIT` I have all my words in their own cells across the row. But since I would like to `SORT` them, they should really be in a column. Remember how `TRANSPOSE` can turn a row into a column?

By wrapping the `SPLIT` function that broke the string into words in a `TRANSPOSE` function, the split-up words are arranged as a column.

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

That's a column that I can now `SORT`!

### Step Three: `SORT`

`SORT` can do a lot more than just alphabetize, but that's all I need it for today.

When I wrap the split & transposed words in a `SORT` function, they are alphabetically sorted.

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

The hard part is done! But they're still in a column, and the challenge was to return it all in one cell. So there's just one more thing left to do. `JOIN` the words back together.

### Step Four: `JOIN`

`JOIN` is like the opposite of `SPLIT`. Instead of breaking a string up at a delimiter, it creates a string from values (aka, concatenates the values) with a delimiter.

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

That brings the split, transposed, and sorted list of values together into a single, comma-separated list โ all in one cell ๐

Thanks to Ben Collins for the challenge, I'm looking forward to the next one!