Sher has data with text months such as Jan'20, Feb'20, and so on. How can you sort those months by date? Today, a trick from Sam Radakovitz using text months as a custom list and then sorting by a custom list.
Transcript of the video:
[ Theme Music ].
Learn Excel from MrExcel Podcast Episode 2353.
Sort text months in the form of Jan'20 and so on.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today's, question sent in by Sher.
Sher has months that are text like this: Jan'20, Oct'24.
He wants to be able to sort those.
I guess I have a video that he found that he says does not work in that scenario.
So we can see that if we would sort this. With Data, A to Z.
April comes first because of "A". It is alphabetical.
So the whole trick is to create a list of all of the possible months.
You can use up to 254 items, so do twenty years worth of months.
That way, you will never have to worry about this again.
Select the list of the months in the correct sequence.
And then File, Options. On the left hand side, go to Advanced.
On the right side, scroll all the way down to the bottom.
You will see a button there called Edit Custom Lists.
Because I pre-selected the range M9:M128, I can just simply click Import.
And I will have a list of all those. Click OK.
And Click OK.
At this point, you don't need that custom list anymore. You can just delete it.
And if you want to see how I created that custom list, wait for the outtake after the video.
In the outtake, you can see how to quickly create the list.
What we are going to do now.
Go to the Sort dialog box.
Sort on Sold. Yes, sort on Cell Values.
But then for the order, choose Custom List.
Choose the list we just created.
Click OK. Click OK.
And it is now arranged oldest to newest.
It is an amazing trick.
Thanks to Sam Rad on the Excel team for giving me that tip years ago.
The current shirts feel like a 50/50 blend of cotton and sandpaper.
If you would just SUBSCRIBE, I could quit nagging you about this.
Check out my new book, MrExcel 20/20. It is full of tips like this.
Click the "i" in the top right hand corner.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♫ ] Tips and tricks get your spreadsheet fix from MrExcel.com [ ♪ ].
Well, hey, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel Podcast Episode 2353.
Sort text months in the form of Jan'20 and so on.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today's, question sent in by Sher.
Sher has months that are text like this: Jan'20, Oct'24.
He wants to be able to sort those.
I guess I have a video that he found that he says does not work in that scenario.
So we can see that if we would sort this. With Data, A to Z.
April comes first because of "A". It is alphabetical.
So the whole trick is to create a list of all of the possible months.
You can use up to 254 items, so do twenty years worth of months.
That way, you will never have to worry about this again.
Select the list of the months in the correct sequence.
And then File, Options. On the left hand side, go to Advanced.
On the right side, scroll all the way down to the bottom.
You will see a button there called Edit Custom Lists.
Because I pre-selected the range M9:M128, I can just simply click Import.
And I will have a list of all those. Click OK.
And Click OK.
At this point, you don't need that custom list anymore. You can just delete it.
And if you want to see how I created that custom list, wait for the outtake after the video.
In the outtake, you can see how to quickly create the list.
What we are going to do now.
Go to the Sort dialog box.
Sort on Sold. Yes, sort on Cell Values.
But then for the order, choose Custom List.
Choose the list we just created.
Click OK. Click OK.
And it is now arranged oldest to newest.
It is an amazing trick.
Thanks to Sam Rad on the Excel team for giving me that tip years ago.
The current shirts feel like a 50/50 blend of cotton and sandpaper.
If you would just SUBSCRIBE, I could quit nagging you about this.
Check out my new book, MrExcel 20/20. It is full of tips like this.
Click the "i" in the top right hand corner.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♫ ] Tips and tricks get your spreadsheet fix from MrExcel.com [ ♪ ].
Well, hey, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
