How do you sort a column in Microsoft Excel by the number of characters? This video was inspired by a question originally posted at Quora: How do you sort a column in Microsoft Excel by the number of characters?
Table of Contents
(0:00) Welcome
(0:13) SORTBY
(0:49) Problems!
(1:06) VSTACK
(1:30) Copy & Paste Values
(1:47) Easy Helper Column
Table of Contents
(0:00) Welcome
(0:13) SORTBY
(0:49) Problems!
(1:06) VSTACK
(1:30) Copy & Paste Values
(1:47) Easy Helper Column
Transcript of the video:
Great one today. How to sort a column by the number of characters.
Just a juicy question here, and initially I thought of elegant and more elegant.
But I think in the end, we just need to go simple, right?
All right, here's the elegant way. =SORTBY.
Specify this whole range, excluding the headings. What are we going to sort by?
We're going to sort by the length, LEN, of column E, and then you have a choice here.
Either one for shortest to longest, like that.
So that'll bring HP to the top, and then IBM, Proctor and Gamble, all the way down to SBC Communications.
Or you can edit that to go the other way, which is minus one for descending, and that will bring the longest item to the top. All right?
But there's problems here. First off, we're missing the heading.
These dates that were formatted as dates are now formatted as general.
So I mean, that's easy enough to fix. No hassle there.
Adding the headings in, we'd have to VSTACK A1 to I1, right?
So now we get the headings, but we're losing the conditional formatting here. We're losing the formulas that are there.
Later on, if this revenue number's going to change, that profit needs to recalculate.
So there's all kinds of reasons why this is just a bad idea.
If we would do Edit Paste Special, well, I don't even know what we would do.
We'd have to do Paste Special Values. Click okay.
We still lose the formulas, but at least the conditional formatting keeps working.
So you know what? I reject the elegant way.
Here's just the easy way. Come out here and add a Helper column.
The Helper column is going to be equal, LEN of the customer name.
Double click to copy that down.
And then you have a choice, either A to Z for shortest the longest, or Z to A for longest to shortest.
And then if you want, delete the Helper column. I mean, that doesn't screw up the formulas.
It doesn't ruin the date format. It doesn't ruin the conditional formatting.
For this question, this method, using the Helper column, is the easy way to go.
All right, well, hey, thanks for sending that question in, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
Just a juicy question here, and initially I thought of elegant and more elegant.
But I think in the end, we just need to go simple, right?
All right, here's the elegant way. =SORTBY.
Specify this whole range, excluding the headings. What are we going to sort by?
We're going to sort by the length, LEN, of column E, and then you have a choice here.
Either one for shortest to longest, like that.
So that'll bring HP to the top, and then IBM, Proctor and Gamble, all the way down to SBC Communications.
Or you can edit that to go the other way, which is minus one for descending, and that will bring the longest item to the top. All right?
But there's problems here. First off, we're missing the heading.
These dates that were formatted as dates are now formatted as general.
So I mean, that's easy enough to fix. No hassle there.
Adding the headings in, we'd have to VSTACK A1 to I1, right?
So now we get the headings, but we're losing the conditional formatting here. We're losing the formulas that are there.
Later on, if this revenue number's going to change, that profit needs to recalculate.
So there's all kinds of reasons why this is just a bad idea.
If we would do Edit Paste Special, well, I don't even know what we would do.
We'd have to do Paste Special Values. Click okay.
We still lose the formulas, but at least the conditional formatting keeps working.
So you know what? I reject the elegant way.
Here's just the easy way. Come out here and add a Helper column.
The Helper column is going to be equal, LEN of the customer name.
Double click to copy that down.
And then you have a choice, either A to Z for shortest the longest, or Z to A for longest to shortest.
And then if you want, delete the Helper column. I mean, that doesn't screw up the formulas.
It doesn't ruin the date format. It doesn't ruin the conditional formatting.
For this question, this method, using the Helper column, is the easy way to go.
All right, well, hey, thanks for sending that question in, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
