Alphabetize a Comma-Separated List in Google Sheets
Tags: spreadsheets, googlesheets
Published: 2020-01-20 21:31:06

---
title: Alphabetize a Comma-Separated List in Google Sheets
published: true
description: my answer to the Formula Challenge #3 in Ben Collins' newsletter
tags: spreadsheets, googlesheets
cover_image: https://thepracticaldev.s3.amazonaws.com/i/e9qsvul8c63x5l99fjtb.jpg
---

I subscribe to Ben Collins' "[Google Sheets Tips](https://www.benlcollins.com/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`](https://support.google.com/docs/answer/3094262?hl=en) 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](https://docs.google.com/spreadsheets/d/1-bM_DVXrMv0_O_fAe5DVU_yRQnUNCAK9E7RRK1Q4K0k/edit?usp=sharing). 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`](https://support.google.com/docs/answer/3094136?hl=en) function breaks text in a cell into pieces based on a [delimiter](https://en.wikipedia.org/wiki/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,",")`

![a spreadsheet with a split formula splitting 5 words into separate cells inside a row.](https://thepracticaldev.s3.amazonaws.com/i/2v24tdl4q9nog1z409sm.png)

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,","))`

![a spreadsheet with a transpose function, which displays the result of a split function in a column instead of its default row format.](https://thepracticaldev.s3.amazonaws.com/i/wuovwsbec2i5b8tng7hx.png)

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


### Step Three: `SORT`

[`SORT`](https://support.google.com/docs/answer/3093150?hl=en) 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,",")))`

![a spreadsheet formula with a sort function alphabetizing a list.](https://thepracticaldev.s3.amazonaws.com/i/kw5lp7jsch34cdzciv53.png)

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`](https://support.google.com/docs/answer/3094077?hl=en) is like the opposite of `SPLIT`. Instead of breaking a string up at a delimiter, it creates a string from values (aka, [concatenates](https://en.wikipedia.org/wiki/Concatenation) the values) with a delimiter. 

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

![a spreadsheet formula with a join function concatenating an alphabetized list.](https://thepracticaldev.s3.amazonaws.com/i/e7xuw8cof51q6yp9i1ie.png)

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](https://twitter.com/benlcollins) for the challenge, I'm looking forward to the next one!