Sort text by length

To sort text strings by length in ascending or descending order, you can use a formula based on the SORTBY and LEN functions. In the example shown, the formula in D5 is:

=SORTBY(B5:B15,LEN(B5:B15),-1)

which sorts the text values in column B by string length, in descending order.

Explanation

The SORTBY function can sort values in a range with an array that doesn't exist on the worksheet.

In this example, we want to sort the values in B5:B15 by the number of characters each string contains. Working from inside out, we use the LEN function to get the length of each value:

LEN(B5:B15) // get length of all strings

Because we give LEN an array with 11 values, we get an array with 11 lengths:

{5;7;14;6;5;13;9;4;8;6;11}

Each number represents the character length of a value in B5:B11.

This array is returned directly to the SORTBY function as the by_array1 argument:

=SORTBY(B5:B15,{5;7;14;6;5;13;9;4;8;6;11},-1)

The SORTBY function allows sorting based on one or more "sort by" arrays, as long as dimensions are compatible with the source data. In this case, there are 11 rows in the source data, and 11 rows in the array returned by LEN, so the requirement is met.

The SORTBY function uses the array of lengths returned by LEN to sort the values in B5:B15, and returns sorted results to D5 in a dynamic array. Because the sort order is set to -1, the values are sorted in reverse (descending) order by length. Use a positive 1 to sort in ascending order.