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.

**Table of Contents**hide

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

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

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

`=(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**

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