Method 1 – Using the LEN Function and a Helper Column
Steps:
- Create a Helper column left to the main data table.
- Enter the following formula in the top cell of the Helper column:
=LEN(C5)
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.
- 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.
- 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Ā
Steps:
- 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:
=SORTBY(B5:C14,LEN(B5:B14),-1)
- 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
- How to Sort by Last Name in Excel
- How to Sort Duplicates in Excel
- How to Sort Unique List in Excel
- How to Sort Numbers in Excel
- How to Sort Merged Cells in Excel
- How to Sort Merged Cells of Different Sizes in Excel
- How to Arrange Numbers in Ascending Order in Excel Using Formula
<< Go Back toĀ Sort in ExcelĀ |Ā Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
