This post will guide you how to sort list of cells in excel by word count from the smallest to the biggest. How do I sort on word number of cells with a formula in a column in excel.
Table of Contents
1. Sort List of Cells by Word Count using formula
If you want to sort the list by word count in excel, you need to create an excel formula based on the LEN function and the SUBSTITUTE function.
For example, you want to sort the cells in the range B1:B5, just do the following steps:
#1 type this formula into the formula box of Cell B1, then press Enter key in your keyboard. And drag the AutoFill handler over the other cells to apply this formula to count the word number of cells.
=LEN(B1)-LEN(SUBSTITUTE(B1," ",""))+1
![sort cell by word count](https://www.excelhow.net/wp-content/uploads/2018/07/sort-cell-by-word-count.gif)
#2 go to DATA tab, click Sort A to Z command under Sort&Filter group.
![sort cell by word count2](https://www.excelhow.net/wp-content/uploads/2018/07/sort-cell-by-word-count2.gif)
#3 select Expand the Selection in the Sort Warning dialog. Click Sort button.
![sort cell by word count3](https://www.excelhow.net/wp-content/uploads/2018/07/sort-cell-by-word-count3.gif)
#4 you will see that the cells have been sorted by word count as below.
![sort cell by word count4](https://www.excelhow.net/wp-content/uploads/2018/07/sort-cell-by-word-count4.gif)
2. Sort List of Cells by Word Count using VBA Macro
Now, let’s explore a more automated approach using VBA code to sort a list of cells by word count. This method allows for greater flexibility and efficiency in handling larger datasets. Follow these steps to implement a VBA macro to sort your data.”
Press “Alt + F11” to open the Visual Basic for Applications editor.
In the VBA editor window, right-click on any item in the Project Explorer pane.
Select “Insert” and then choose “Module” to insert a new module into the project.
Copy and paste the provided VBA code into the newly created module.
Sub SortByWordCount()
Dim sourceRange As Range
Dim sortRange As Range
' Prompt the user to select a range of cells
On Error Resume Next
Set sourceRange = Application.InputBox("Select the range of cells to sort by word count", Type:=8)
On Error GoTo 0
' Check if the user selected a valid range
If sourceRange Is Nothing Then
MsgBox "No range selected. Operation cancelled.", vbExclamation
Exit Sub
End If
' Add a helper column to calculate word count
sourceRange.Offset(0, 1).Formula = "=LEN(TRIM(" & sourceRange.Address & "))-LEN(SUBSTITUTE(TRIM(" & sourceRange.Address & "), "" "", """"))+1"
' Define the sort range
Set sortRange = sourceRange.Resize(, 2)
' Sort the range by word count
With sortRange.Worksheet.Sort
.SortFields.Clear
.SortFields.Add Key:=sortRange.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' Clear the helper column
sortRange.Columns(2).ClearContents
End Sub
This code will add a helper column to the selected range to calculate the word count. Then, it sorts the range based on the calculated word count. Finally, it clears the helper column after sorting.
Close the VBA editor window.
Press “Alt + F8” to open the “Macro” dialog box.
Select the macro you just created from the list.
Click “Run” to execute the macro.
Select the range of cells containing the data you want to sort.click ok button.
you should see that the list of cells is sorted based on the word count, as specified by the VBA macro.
3. Video: Sort List of Cells by Word Count
This Excel video tutorial, we’ll explore two methods to sort a list of cells by word count. We’ll begin by using a formula combined with the Sort & Filter feature to achieve the desired sorting. Then, we’ll delve into a more advanced approach using VBA code to automate the process.
4. Related Functions
- Excel Substitute function
The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE (text, old_text, new_text,[instance_num])…. - 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
You must be logged in to post a comment.