How to Sort Column by Length of Cell Characters in Excel

This post will guide you how to sort column values by length of the characters in one column in Excel. How do I sort a list of column by character length in Excel 2013/2016.

Sorting by the length of cell characters is a useful feature when you want to quickly identify cells with a certain length of text, such as cells with too much or too little information.

1. Sort Column by Length of Cell Characters using Sort Feature

Assuming that you have a list of data in column A which contain text string with different length. And you want to sort those data by character length in Column A. How do do it. This post will show you how to sort data by length of cell values.

To sort Column by length of Cell characters using Sort function, you still need to use a helper column to calculate the number of characters in Column A using LEN function, then use Sort option to sort those data. Do the following steps:

Step1: enter the following formula based on LEN function in the adjacent Column B to calculate the length of text string in Column A. Then drag the AutoFill Handle in Cell B1 down to other cells to apply this formula.

=LEN(A1)
sort column by length 1

Step2:  go to Data tab in the Excel Ribbon, and click Sort button under Sort & Filter group. And the sort Warning dialog will open.

sort column by length 2

Step3: select Expand the selection option in the Sort Warning dialog box. And click Sort button. Then the Sort dialog box will appear.

sort column by length 3

Step4: select Column B in the dropdown list box of Sort by, and choose Cell Values in the drop down list box of Sort on, choose the sort order as Smallest to largest in the Order drop down list box. Click OK button

sort column by length 4

Step5: you should see that column A has been sorted by the length of Cell values. Now you can delete the helper column.

sort column by length 5

By sorting the column based on the length of cell characters, you can easily find cells with similar content and make changes to your spreadsheet accordingly.

2. Related Functions

  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…

Leave a Reply