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.
When I see a cell with comma-separated values in it, I know I'm probably gonna have to do a
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:
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.
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.
That's a column that I can now
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.
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.
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!