Microsoft Excel

Learn How to Sort by Text Length in Microsoft Excel

February 7, 2023
3 Min Read

Learn How to Sort by Text Length in Microsoft Excel

The main purpose of this formula is to sort text strings by length in ascending or descending order. Here we will learn how to sort by text length in the given data range in the workbook in Microsoft Excel.

That implies, with the help of a formula based on the SORTBY, and LEN functions, you can able to sort text strings by a length in ascending or descending order.

So, with the help of this formula, you can able to sort by text length in the given data range in the workbook in Microsoft Excel.

 

General Formula to Sort by Text Length

=SORTBY(data,LEN(data),-1)

 

The Explanation to Sort by Text Length

Learn How to Sort by Text Length in Microsoft Excel

So we know that with the help of the given formula above, you can able to sort text strings by length in ascending or descending order. Here we will learn how to sort by text length in the given data range in the workbook in Microsoft Excel.

As we know that the SORTBY function allows the sorting based on one or more “sort by” arrays, as long as dimensions are compatible with the source data. In the example given here, our goal is to sort the values in the B column by the number of characters each string contains.

When working from the inside out, we use the LEN function to get the length of each value. So, with the help of this formula, you can able to sort text strings by length in ascending or descending order.

So now you have learned how to sort by text length in the given data range in the workbook in Microsoft Excel.

 

 

The SORTBY function in Microsoft Excel can be used to sort a range of cells by their text length in either ascending or descending order. Here are the steps to use the SORTBY function to sort by text length:

  1. Select the range of cells you want to sort.
  2. In the formula bar, enter the SORTBY function. The syntax for SORTBY is:

    SORTBY(array, by_array, [sort_order])

    • array is the range of cells to be sorted.
    • by_array is the range of cells by which to sort the array. In this case, you can use the LEN function to calculate the length of each cell’s text. So the by_array parameter should be LEN(array).
    • [sort_order] is an optional parameter that specifies whether to sort the array in ascending or descending order. If omitted, it defaults to ascending order. To sort in descending order, set this parameter to -1.
  3. Replace array and by_array in the formula with the actual ranges you selected in step 1. For example, if you selected cells A1:A10, the formula would look like this:

    =SORTBY(A1:A10,LEN(A1:A10))

    This formula sorts the cells A1 to A10 in ascending order based on their text length.

  4. Press Enter to apply the formula. The selected cells will now be sorted by text length.

By using the SORTBY function in this way, you can quickly and easily sort cells by text length in Microsoft Excel.