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 an Excel column.
Download Practice Workbook
5 Methods to Count Words in Excel Column
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 Selling 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
Here, C5 refers to the text The Black Swan (text argument).
- 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)
- 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 specific words in a cell 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).
- 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
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.
I hope these useful methods mentioned above will answer your queries on how to count words in the Excel column. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles on the ExcelDemy website.