However, there is no inbuilt functionality in Excel to sort the cells based on the length of the text in the cells. In one of my Excel training sessions, I was asked whether it is possible to ‘sort by the length of text in the cells in Excel or not. I could come up with two simple methods to do this (there could be more I am sure). In this tutorial, I’m going to share these two simple methods you can use to quickly sort by the length in Excel.
Sort by Length Using the LEN Function + Sort Functionality
Below I have a data set where I have the names of some people in column A, and I want to sort this data set based on the length of the name in the cell. So, I want the shortest name to be at the top and the longest one at the bottom. In this method, I will show you how to do this using the helper column along with the inbuilt sort functionality in Excel. The logic here would be simple, first find out the length of the name in each cell in the helper column, and then use that helper column to sort the data. Below are the steps to do this: The above steps would instantly sort the data based on the length of the name in each cell in column A. Once you are done with the sorting, you can delete the helper column. Pro Tip: In case you need to keep the original data as well, it’s best to create a backup copy of the dataset Note: I have used the LEN function to find out the total number of characters in each cell. In case the cells have leading, trailing or double spaces in between the names, these would also be counted. This can give you unexpected sort results, so make sure to remove any extra spaces.
Sort by Length Using the SORTBY Formula (New Function in Office 365)
If you’re using the new version of Excel (that comes with the Microsoft 365 subscription), you would have access to additional sort functions that make it really easy to sort based on the length of text. Below I have the same data set where I have the names in column A and I want to sort this data based on the length of the name. Here is the formula that will do this for me: Enter the above formula in the cell where you want the result. Since this is an array formula, the result would spill over and show the result in the cells below the one where the formula is entered. In this example, I have entered this formula in cell B2. Now, let me quickly explain how this formula works. In the above SORTBY function I have used three arguments:
The array that needs to be sorted – this would be the names in column A that needs to be sorted based on the length of the name The array based on which you need to sort the first array – this is the criteria based on which we need to sort the names data set. Since I want to sort this based on the length of the name, I have used the LEN function to first calculate the length of the name in each cell and then use that as the criteria to sort the name’s data Sort order – I have used 1 here as I want the sorting to be done in ascending order
Also, here are some important things you need to know when using this formula to sort based on text length:
Since this is the result of an array formula, you will not be able to change or delete any one cell that has the result of the formula. You can delete the entire result but cannot edit or delete specific elements of the resulting array When you enter the SORTBY formula and hit enter, the result of the formula spills over to the cells below the one where you have entered the formula. If any of the cells (that is supposed to be filled with the resulting array) already has anything in it, the formula would give you the SPILL error. To correct this error, you will have to make sure that the cells are empty so that they can be occupied by the result of the SORTBY formula
So these are two simple ways you can use to quickly sort based on the text length in the cells in Excel. If you’re using an older version of excel, you can use the LEN function along with the sort functionality, and if you’re using the newer versions of Excel and have access to the SORTBY function, you can get this done with a single formula. I’m sure you can also get this done using VBA or Power Query, but I wanted to keep it simple and show you the two easiest methods that I could come up with. Other Excel tutorials you may also like:
How to Sort by the Last Name in Excel How to SORT in Excel (by Rows, Columns, Colors, Dates, & Numbers) How to Sort By Color in Excel (in less than 10 seconds) How to Sort Worksheets in Excel using VBA (alphabetically) How to Sort Data in Excel using VBA Automatically Sort Data in Alphabetical Order using Formula
