How to Sort in Excel by Number of Characters (2 Methods)

Method 1 – Using the LEN Function and a Helper Column


  • Create a Helper column left to the main data table.
  • Enter the following formula in the top cell of the Helper column:

Sort in Excel by Number of Characters with LEN Function

The LEN function returns the number of characters of a selected cell value. Here, I selected cell C5 to return the number of characters of the value “Tom Duff”.

  • Press ENTER.

Sort in Excel by Number of Characters with a Helper Column

  • Keep your mouse cursor at the right-bottom corner of the cell where you’ve entered the formula.
  • The Fill Handle icon will appear.
  • Pull down that icon (+)  throughout the rest of the cells to AutoFill the formula.

  • Select the Entire Helper column and go to Data > Sort & Filter > Sort.

Sort in Excel by Number of Characters

  • The Sort Warning dialog will appear. Make sure the Expand the selection command is selected.
  • Press the Sort button.

  • The Sort dialog box will appear.
  • Select the Helper column in the Column Sort by drop-down.
  • Select Smallest to Largest from the Order drop-down.
  • Press the OK button to apply the changes.

Now you will see your data have been sorted by the number of characters as in the picture below:

Read More: How to Put Numbers in Numerical Order in Excel

Method 2 – Using the SORTBY Function 


  • Create an identical data table to your original data table without the contents.

  • Enter the following formula in the very first cell of the blank data table:

Use SORTBY Function to Sort in Excel by Number of Characters

  • Press ENTER to enter the formula above.

You will have all the data sorted by the number of characters as in the picture below:

Formula Breakdown

  • LEN(B5:B14) calculates the length of the contents inside each of the cell addresses in the range.
  • -1 sorts data in descending order. To sort data in ascending order, you have to use 1.
  • SORTBY(B5:C14,LEN(B5:B14),-1) sorts data in the range B5:C14 based on the numerical data from LEN(B5:B14) in descending order.

Read More: How to Sort by Name in Excel

Practice Section

Use this practice section to practice all the methods discussed in this article.

Download the Practice Workbook

You can download the Excel file from the following link and practice.


Related Articles

<< Go Back to Sort in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF