which sorts the text values in column B by string length, in descending order. 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: Because we give LEN an array with 11 values, we get an array with 11 lengths: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
