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.

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

#2 go to DATA tab, click Sort A to Z command under Sort&Filter group.

#3 select Expand the Selection in the Sort Warning dialog. Click Sort button.

#4 you will see that the cells have been sorted by word count as below.

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