How to Count Words in Excel Column (5 Useful Ways)

Let’s consider a dataset with 2 columns, where the first column shows the Product ID and the second column shows a list of Best Seller Books. We’ll use this in B4:C13 cells for the first four methods.

Method 1 – Count the Number of Words in a Cell with the LEN Function

• Use the following formula in the result cell:

Formula Breakdown:

• The SUBSTITUTE function replaces all the spaces in the text in the C5 cell.
• The LEN function returns the length of the text without any spaces in between.
• We subtract the length of the text without any space from the total length of the text and add 1 to get the total word count.
• The TRIM function removes any unnecessary spaces from the cell.

Method 2 – Using the SUMPRODUCT Function to Count Words in a Column

• Use the following formula inside the result cell.

Here, C5:C13 represents the range of Best Seller Books (array1 argument).

Formula Breakdown:

• This formula is similar to the previous method, so the same functions are used however, there is a slight modification in the last step.
• The formula for the word count is nested inside the SUMPRODUCT function, which adds up all the word counts in the range C5:C13 and shows the total count in the E5 cell.
• Press Ctrl + Shift + Enter to apply the formula in older Excel versions since it’s an array formula.

Method 3 – Counting Specific Words in a Column

• The word we’ll be searching is located in F5.
• The formula for the F5 cell is below:

Here, F4 refers to The (old_text argument).

Formula Breakdown:

• Here, dividing the total number of words returned by the SUMPRODUCT function by the number of words of the text gives back the number of times the word or text appears in the column.

Method 4 – Creating a Custom Function to Count the Number of Words

• Go to the Developer tab and then to Visual Basic.

• Insert a Module where you’ll paste the VBA code.

• You can copy the code from here and paste it into the window as shown below.

Function WordCount(rng As Range) As Integer
'This function helps you to count the number of words'
WordCount = UBound(Split(rng.Value, " "), 1) + 1
End Function

• Close the VBA window and return to your worksheet.
• Type an Equal sign followed by the newly-made WordCount function.
• The function takes only one argument so enter any cell reference containing text and press Enter.

Method 5 – Count Specific Words in a Cell with the LEN Function

Suppose we have a dataset shown below with only one column containing text strings.

Case 5.1 – Count Specific Words in a Cell (Case-Sensitive) with SUBSTITUTE

• Use the following function:

In this example, the B5 cell refers to the Text column, while the E4 cell represents the text which is being counted.

Case 5.2 Count Specific Words in a Cell (Case-Insensitive) with UPPER or LOWER Inside SUBSTITUTE

• Use the following function:

In this formula, the UPPER function converts the target word and the searching range to the same case.

Download the Practice Workbook

<< Go Back to Count Words | Learn Excel