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.

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

 

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)…

 

 

Related Posts

Abbreviate Names Or Words in Excel

As an MS Excel user, you might have come across a task where you need to abbreviate different names or words, and there are also possibilities that you might have done this task manually by assuming that there isn't any ...

Extract Last Two Words From Multiple Cells

Just assume that you have a few cells containing values/words and you want to extract the last two words from each cell into another separate cell; then you might think that it's not a big deal; because you would prefer ...

Extract Multiple Lines From A Cell

Suppose that you have listed some text in a single cell which is separated by the line break(you can do it by pressing ALT + ENTER after entering the text), and now you want to extract multiple lines of text ...

How to Count Cells More Than Certain number of Characters in Excel

This post will guide you how to count the number of cells that more than a certain number of characters in a given range cells using a formula in Excel 2013/2016.How do I count cells with length that greater than ...

How to Split Cells by the First Space in Texts in Excel

Sometimes we may use space to separate texts to different groups in one cell, if we want to split this cell to multiple columns refer to space, we can implement this via ‘Text to Columns’ feature. But is there any ...

Comments

So empty here ... leave a comment!

Leave a Reply

Sidebar