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.

**Table of Contents**hide

**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**

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

`=LEN(TRIM(C5))-LEN(SUBSTITUTE(C5," ",""))+1`

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.

`=SUMPRODUCT(LEN(TRIM(C5:C13))-LEN(SUBSTITUTE(C5:C13," ",""))+1)`

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 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.

`=SUMPRODUCT((LEN(C5:C13)-LEN(SUBSTITUTE(C5:C13, F4,"")))/LEN(F4))`

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.

**Read More:** **How to Count Specific Names in Excel (3 Useful Methods)**

**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.

`=(LEN(B5)-LEN(SUBSTITUTE(B5,$E$4,"")))/LEN($E$4)`

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.

`=(LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),UPPER($E$4),"")))/LEN($E$4)`

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.

**Read More:** **How to Count Specific Words in a Column in Excel (2 Methods)**

**Conclusion**

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.