- Got Sheet
- Posts
- Sort a Cell
Since last we spoke I’ve…
Been training for a fast marathon
Stayed up till 2am
Finished 2 a couple books (check out my yearly progress here)
Now to the sheets…
I fired up Excel for this week’s little hack, but you can do the same thing easily enough in Sheets as well. The only curious difference is one of semantics. The names of functions are different depending on the program you’re using, but they do the same things.👇
Sheets | Excel |
|---|---|
JOIN() | TEXTJOIN() |
SPLIT() | TEXTSPLIT() |
Goal
Now, what we want to do is sort the contents of a cell. You can see how we work through this in the picture below. On the left we’ve got a cell with four movies listed. On the right, are those same movies sorted alphabetically.
We can do this with one formula, and here it is (Excel version) if you don’t want the full breakdown below👇
=TEXTJOIN(", ",TRUE,SORT(TRANSPOSE(TEXTSPLIT(A2,", "))))Method
We’ve got to tear down this problem and rebuild it. The built in SORT function will sort a range or an array, but not the contents of our cell.
So, we’ll split our text into four separate cells, we’ll transpose that so it’s in four rows of one column (SORT breaks when we try to use it horizontally across a row), we’ll sort the transposed result, and then join all of that back together using commas.
We have to nest all those functions within each other for things to work out happily.
Happiness()
But like all joyful Excel functions, the end result is overflowing with satisfaction, happiness, and in this case, stellar films.
The 39 second video walkthrough I did is below 👇
Thank you so much!
It means a lot that you’ve read this, and I hope it’s informed and/or entertained you for a few moments today!
Would love to say hi. Here are the best places to find me:
Need More Help?
Need a powerful automation tool for Google Sheets? Try Coefficient to automatically import data and sync with your business systems.
Hire an expert to help complete your next Google Sheets, Apps Script or Google Workplace project.