Sort Column by Length of Text in Google Sheets

To sort a column by length of text, you can either use the QUERY function or the SORT menu in Google Sheets. Both have their pros and cons.

The output of the QUERY function will be a new range, equal to the size of the sorted column, whereas the SORT menu command requires helper column support. Here you will find both options.

Sort Column by Length in Google Sheets Using the QUERY Function

Assume you have a list of book titles in column B. In that case, you can use the following formula in the first row of any empty column:

=QUERY(HSTACK(B1:B, ARRAYFORMULA(LEN(B1:B))), "SELECT Col1 ORDER BY Col2 DESC", 1)

This places the largest string at the top — that is, it sorts the book titles by length of text in descending order.

Sort Column by Length in Google Sheets with the QUERY Function

If you want to sort the strings in ascending order based on length, use the following formula instead:

=QUERY(HSTACK(B1:B, ARRAYFORMULA(LEN(B1:B))), "SELECT Col1 WHERE Col1 IS NOT NULL ORDER BY Col2", 1)

Note: The formula assumes the list has a title in the first row. If not, replace 1 at the end of the formula with 0.

Formula Explanation

We used the HSTACK function to append the length of the strings with column B, creating a two-column array where:

  • The first column contains the list to sort.
  • The second column contains the length of each item.

The QUERY function then sorts the first column based on the second.

When sorting in ascending order, we must remove the empty cells — that’s where "WHERE Col1 IS NOT NULL" comes into play.

Why Not Use the SORT Function?

We use QUERY over SORT because QUERY can flexibly include or exclude the header row when sorting — something the SORT function can’t handle directly.

Sort Column by Length in Google Sheets Using the SORT Menu Command

When using the SORT menu command, we need to generate a helper column.

Make sure you have a title at the top of the list. Then enter the following formula in cell C1:

=VSTACK("Helper", ARRAYFORMULA(LEN(B2:B6)))
Helper Formula to Generate Length of Text

Steps to SORT:

  1. Select B1:C6.
  2. Click Data > Sort Range > Advanced Range Sorting Options.
  3. In the window that appears, check Data has a header row.
  4. Sort by Helper.
  5. Choose A to Z or Z to A depending on your desired sorting order.
  6. Click Sort.
Sort Column by Length in Google Sheets Using the SORT Menu

This will sort the list by length of text.

Does Sort by Length Apply to Text Only?

Nope!

Since the LEN function returns the length of text, numbers, and even special characters, you can apply this method to any data type in the column.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Highlight the Smallest N Values in a Column in Google Sheets

Want to visually spotlight the lowest values in your data? In this tutorial, you'll...

Sort Each Row Individually in Excel Using a LAMBDA Formula

Sorting rows in Excel typically refers to rearranging entire datasets based on values in...

Sort by Field Labels Using the SORT and XMATCH Combo in Excel

Want to sort your Excel data by column names instead of column positions? Learn...

How to Sort Pie Slices in Google Sheets

To sort pie slices in a pie chart, you need to sort the data...

More like this

Highlight the Smallest N Values in a Column in Google Sheets

Want to visually spotlight the lowest values in your data? In this tutorial, you'll...

How to Sort Pie Slices in Google Sheets

To sort pie slices in a pie chart, you need to sort the data...

Filter Items Unique to Groups in Google Sheets

In this tutorial, we'll learn how to filter items unique to groups in Google...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.