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

Whilst analyzing large sets of data in Excel, eventually, you may need to count the number of words in a column. This article provides 5 useful methods on how to count words in Excel column.

How to Count Words in Excel Column: 5 Useful Methods

Although Microsoft Excel does not offer any built-in functions to count the number of words, we can nonetheless, combine a few functions to develop a formula to count the words in a column.

Let’s consider a dataset with 2 columns where the first column shows the Product ID, and the second column shows a list of the Best Seller Books. Now, we’ll use the dataset shown below (in B4:C13 cells) for the first, second, third, and fourth methods.

So, let’s explore these methods one by one.

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

Counting the number of words in a cell combines the LEN, SUBSTITUTE, and TRIM functions respectively.

Here, C5 refers to the text The Black Swan (text argument).

Formula Breakdown:

• In this formula, the SUBSTITUTE replaces all the spaces in the text in the C5 cell.
• Next, the LEN function returns the length of the text without any spaces in between.
• Lastly, we subtract the length of the text without any space from the total length of the text and add 1 to it to get the total word count.
• Additionally, the TRIM function removes any unnecessary spaces from the cell.

2. Using SUMPRODUCT Function to Count Words in a Column

To calculate the total number of words present in a range of cells we append the word count formula inside the SUMPRODUCT function.

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

Formula Breakdown:

• First of all, this formula is similar to the previous method, so the same functions are used however, there is a slight modification in the last step.
• Next, the formula for 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.
• As a note, please remember to press CTRL + SHIFT + ENTER.

3. Counting Specific Words in a Column

Taking a similar approach, you can also count the number of occurrences of a particular word in a column. Likewise, the SUMPRODUCT function can be combined with the formula to count the number of times that word appears in the range.

The formula for the F5 cell is the following.

Here, F4 refers to The (old_text argument).

Formula Breakdown:

• Similar to the previous formula, there is a change at the very end.
• 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.

4. Creating a Custom Function to Count the Number of Words

If you’re wondering, is there a way to define a custom function to count words? Then, VBA has you covered. Just follow these steps.

Step 01: Opening the VBA Editor

• Firstly, go to the Developer tab and then to Visual Basic.

Step 02: Inserting the VBA Code

• Secondly, insert a Module where you’ll paste the VBA code.

• As a note, 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

Step 03: Using the Custom Function

• Thirdly, close the VBA window and return to your worksheet.
• Lastly, type an Equal sign followed by the WordCount function. The function takes only one argument so enter any cell reference containing text and press ENTER.

5. Count Specific Words in a Cell with LEN Function

In the last example, I will show you how to count words in an Excel column with the LEN function.

Suppose we have a dataset shown below (in B4:B7 cell) with only one column containing Text which we want to utilize to illustrate the fifth and the final method. So, let’s start.

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

The SUBSTITUTE function in Excel is case-sensitive as a result, employing this function makes the formula case-sensitive which is shown in the E5 cell as follows.

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

5.2 Count Specific Words in a Cell (Case-Insensitive)

What if you need to consider both the uppercase and lowercase appearance of the specific word? Well, you’re in luck, you can use the same formula with some minor tweaks. To clarify, you can use either the UPPER or LOWER function inside the SUBSTITUTE function to handle both cases this is shown in the E5 cell.

In this formula, the UPPER or the LOWER function converts the target word to the same case regardless, of its case in the string of text.

Download Practice Workbook

Download the following practice workbook to practice along with it.

Conclusion

I hope these useful methods mentioned above will answer your queries on how to count words in Excel column.

<< Go Back to Count Words | Learn Excel