Exceljet

Quick, clean, and to the point

Sort text by length

Excel formula: Sort text by length
Generic formula 
=SORTBY(data,LEN(data),-1)
Summary 

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.

Dynamic Array Formulas are available in Office 365 only.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.