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

Get FREE Advanced Excel Exercises with Solutions!

If you want to sort in Excel by numbers only, you can do so directly using some Excel commands. But in case you are looking for ways to sort by the number of characters, then you have to use additional formulas to do that. In this article, you will learn 2 different methods to sort in excel by the number of characters.


Download Practice Workbook

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


2 Methods to Sort in Excel by Number of Characters

1. Sort in Excel by Number of Characters with LEN Function and a Helper Column

The most common way of sorting data in Excel by the number of characters is to use a LEN function and a Helper column. Then based on the data of the Helper column, you will be able to sort by the number of characters in Excel.

Now let’s see the whole procedure step by step.

❶ First of all, create a Helper column left to the main data table.

❷ Then insert the following formula in the top cell of the Helper column.

=LEN(C5)

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 so it will return the number of characters of the value “Tom Duff”.

❸ Then hit the ENTER button.

Sort in Excel by Number of Characters with a Helper Column

❹ After that keep your mouse cursor at the right-bottom corner of the cell where you’ve entered the formula.

The Fill Handle icon will appear.

❺ Now pull down that icon (+)  throughout the rest of the cells to AutoFit the formula.

❻ Now select the Entire Helper column and go to Data > Sort & Filter > Sort.

Sort in Excel by Number of Characters

After hitting the Sort button, the Sort Warning dialog will appear.

❼ Make sure the Expand the selection command is selected and then hit the Sort button.

After that, the Sort dialog box will appear.

❽ Select the Helper column in the Column Sort by drop-down.

❾ Then select Smallest to Largest from the Order drop-down.

❿ Finally hit 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 Use Excel Shortcut to Sort Data (7 Easy Ways)


Similar Readings


2. Use SORTBY Function to Sort in Excel by Number of Characters

Microsoft Office 365 has come up with a new function called the SORTBY function. This function can Sort data based on the information that you put inside it.

If you are using an older version of Excel, you can not use this method.

Anyways, let’s get started.

❶ At first create an identical data table of your original data table without the contents.

❷ After that, insert the following formula in the very first cell of the blank data table.

=SORTBY(B5:C14,LEN(B5:B14),-1)

Use SORTBY Function to Sort in Excel by Number of Characters

❸ Finally hit the ENTER button to enter the formula above.

After that, 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 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 Ascending Order in Excel (3 Easy Methods)


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.


Conclusion

To sum up, we have discussed 2 methods to sort in Excel by the number of characters. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy Roy

Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo