When working with Microsoft Excel, you may often come across a situation where you need to sort data based on the length of characters in a cell.
For instance, let’s say you want to sort the products available in an inventory based on their character’s length where the product name with the least characters would appear on the top and the one with the most characters would be at the bottom.
In this Excel tutorial, I will show you two easy methods to sort by length of text in cells in Excel.
Method 1: Sort by Length Using the LEN Function
In this method, I will show you how you can sort the data based on the length of text in the cells using the LEN function.
LEN is a built-in Excel function that returns the number of characters in the input string.
For demonstration purposes, I will use a sample dataset where there are different products listed in column A and their respective quantities in column B.
You can see that the products shown in the dataset above are not sorted by length.
Let’s see how we can sort them by character length using the method shown below.
- Insert a column to the right of column A. This will be our helper column.
To determine the length of characters in a given cell, use the following formula:
To determine the length of characters of the first product (Stapler) the formula would be:
- Write the formula in Excel as shown in the screenshot.
- Now copy the formula in the entire helper column to get the length of characters for each product.
To copy the formula, click and drag the Fill Handle (plus icon) that appears when moving the cursor to the bottom-right of the selected cell.
- Select all cells from cell A2 to cell C13 (excluding column headings). To select a range, select a cell and then drag over the other cells with the left mouse button pressed (or select a cell and then use the shortcut Control + A to select the entire dataset).
- Click on the Data tab in the ribbon
- In the Sort & Filter section, click on the Sort option
- The Sort window will appear as shown below.
- Select Column B from the drop-down menu under Sort by. Column B is where we have the helper column and we will be using this to sort the entire dataset
- Select ‘Values‘ from the drop-down menu under ‘Sort On‘
- Select Smallest to Largest from the drop-down menu under Order.
In case you want to sort in descending order, select Largest to Smallest.
- Click on OK.
This will sort the products based on the length of characters in ascending order as shown below.
You may delete the additional / helper column if you wish or you can keep it and hide it.
Also read: How to Filter by Color in Excel?
Method 2: Sort by Length Using the SORTBY Function
This method uses the SORTBY function to sort a given set of data. The SORTBY function is part of the new Excel Dynamic Arrays family.
Dynamic Arrays are only available in Office 365 and Excel versions 2021 and above.
Excel versions 2019 and below will not have the Dynamic Array functions, so if you’re using an older version of Excel, you won’t be able to use this method.
We will use the same dataset as we did in Method 1 (shown below), and sort this data based on the length of the text in column A.
You can see that the products shown in the dataset above are not sorted by length. Let’s see how we can sort them by character length using the second method shown below.
- Create an identical dataset of the original dataset without the contents as shown.
To sort a given dataset using the SORTBY function, the formula is as follows:
SORTBY(array, by_array_1, [sort_order1], [by_array2, sort_order2],...)
This formula has two main arguments; array & by_array_1. Arguments inside square brackets are optional. I will explain these arguments below.
- array – This argument is the array of values to be sorted. In this case, this would be the products from “Stapler” to “Bulldog clip” i.e. range A2 to A13.
- by_array_1 – This argument is the array on which you want to sort the first array. This is the criteria based on which we need to sort the products dataset. Since I want to sort based on the length of the name, I will use the LEN function to first calculate the length of the product name in each cell and then use that as the criteria to sort the products.
- sort_order1 – This argument is optional. If not provided, the sorting will be done in ascending order. In case you want to sort in descending order, use the value -1.
So the formula would become like so:
- Enter the formula in the cell where you want the result (cell D2 in this example)
You will see all the products sorted in the new dataset as shown.
Since you have used a formula, in case you change the original data set, the sorted data set would automatically update. This is great in case you are creating reports, summaries, or dashboards and you want to show the sorted data in another worksheet which is based on the data in some other sheet
A couple of important things to know when using the SORTBY function (which is a dynamic array function)
- Make sure that the cells that will get the result of the SORTBY function are empty. if the cells are not empty, you will not get the result of the sort by function and would get the SPILL error instead. in case this happens, check the cells to make sure they’re empty, and delete the content in any cell that is not empty
- You cannot edit or change individual elements of the result that you get from a dynamic array formula. For example, in this case, you cannot delete or edit any specific element of the resulting output data but can delete the entire data set. In case you need to edit the cells in the formula output, you need to first convert the formula into values
So in this article, we have seen two different methods to sort a given dataset based on the length of characters.
Method 1 sorts the original dataset using a helper column whereas Method 2 generates an additional (sorted) dataset.
If you have a large dataset and creating an additional dataset may not seem feasible/manageable, go with Method 1. If your dataset is small and either keeping or deleting the original dataset is not a problem, go with Method 2 as it is short and quick.
Also, note that if you are using an older version of Microsoft Excel (version 2019 or below) you will not be able to use the SORTBY function.
In that case, you will have to stick with Method 1.
Other Excel articles you may also like:
- How to Sort a Pivot Table in Excel
- How to Sort by Date in Excel (Single Column & Multiple Columns)
- How to Unsort in Excel (Revert Back to Original Data)
- How to Make all Cells the Same Size in Excel
- How to Filter Multiple Columns in Excel?
- How to Compare Two Cells in Excel? (Exact/Partial Match)
- Switch First and Last Name with Comma in Excel (Flip Names)