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!
Top comments (1)
Thanks for posting this elegant and powerful formula; saved me tons of time! Note to users: If the elements in your list have a space after the comma use this:
JOIN(", ",SORT(TRANSPOSE(SPLIT(A1,", "))))
And if the elements in your list have a space after the comma AND if some elements are open compounds (two words or more separated by space), use this:
JOIN(", ",SORT(TRANSPOSE(SPLIT(A1,", ",FALSE))))