Itâ€™s possible that the instance of a word in a text string repeats over and over again. And you might be curious to count the total occurrence of that specific word in your texts. If itâ€™s so, we recommend you go through the following article carefully. This article will show you two distinct methods that you can use to **count specific words** in a column in Excel.

**Table of Contents**hide

## Download the Practice Workbook

You are recommended to download the practice workbook from the following link and practice along with it.

## 2 Methods to Count Specific Words in a Column in Excel

Letâ€™s assume that you have a booklist with some book names and their corresponding author names. You want to count how many times the word â€śTheâ€ť occurs throughout the Book Names Column.

Now we will describe 2 useful methods to count specific words within a column in Excel. But before that, letâ€™s first understand the basics of counting specific words throughout a text line in Excel.

## The Theory of Counting Specific Words in Excel

Letâ€™s take a text line first. For example â€ś**Visit Exceldemy to Learn Excel**â€ť where we will be counting the instances of the word â€ś**Exceldemy**â€ś. To do so,

**Step-1:** **Count** the total length of the text line first. Which is 30.

**Step-2:** **Count** the total length of the text line without the word â€śExceldemyâ€ť. Which is 21.

**Step-3:** If we **subtract** the result of Step-1 and Step-2, we will find the length of the word â€śExceldemyâ€ť which is 30-21=9.

**Step-4:** **Count** the length of the word â€śExceldemyâ€ť distinctly. Which is 9 again.

**Step-5:** Letâ€™s **divide** the result of Step-3 by the result of Step-4. We will get 1.

Which is the number of instances of the word â€śExceldemyâ€ť within the text line â€śVisit Exceldemy to Learn Excelâ€ť.

Now you know the theory of calculating the number of occurrences of a specific word within a text line. So, letâ€™s learn to write formulas to implement that in Excel.

**1. Count Specific Words in a Column Considering Case**

In this section, you will learn how to count specific words considering the letter cases in a column.

Hereâ€™s the general formula to **count words**:

`=(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")`

**Formula Breakdown**

**LEN(range):** **Calculates** the total length of the original text line.

**SUBSTITUTE(range,â€ťtextâ€ť,â€ťâ€ť):** **Substitutes** the main text with a null value in the place of the specific word intended to count.

**LEN(SUBSTITUTE(range,â€ťtextâ€ť,â€ťâ€ť)):** **Calculates** the length of the substituted main text (The text line without the word intended to count).

**LEN(â€śtextâ€ť):** **Calculates** the length of the word intended to count.

**Choose Values for the Functions**

**range:** Starting and Ending Cell Address of a column selected to run the formula.

**text:** Input the specific word intended to count.

**â€śâ€ť:** Donâ€™t leave any space in between the quotation marks.

Now follow the below steps:

**Step-1: Select** cell D7 to put the count result.

**Step-2: Type** the formula as below:

`=(LEN(B7:B13)-LEN(SUBSTITUTE(B7:B13,"The","")))/LEN("The")`

**Step-3:** Hit the **ENTER** button.

Now youâ€™ve got the count result for the first row in the table.

**Step-4: Drag** the **Fill Handle** icon to the end of the table.

Thatâ€™s it.

Now letâ€™s move on to the next method.

**Read More:** **How to Count Words in Excel Column (5 Useful Ways)**

**Similar Readings**

**COUNTIF Cell That Contains a Specific Text in Excel (Case-Sensitive and Insensitive)****How Do I Count Cells with Text in Excel (5 Methods)****Count If Cell Contains Text in Excel (5 Easy Approaches)**

### 2. Count Specific Words in a Column Ignoring Case

Hereâ€™s the general formula to count words ignoring the letter case:

`=(LEN(range)-LEN(SUBSTITUTE(UPPER(range),UPPER("text"),"")))/LEN("text")`

**Formula Breakdown**

Everything is just the same as the previous formula except the additional **UPPER** function inside the **SUBSTITUTE** function.

This function changes every letter to the upper cases.

Then the **SUBSTITUTE** function trims off the main text.

So, the **LEN** function can calculate the substituted main text line ignoring the letter cases.

Now follow the below steps:

**Step-1: Select** cell D7 to put the count result.

**Step-2: Type** the formula as below:

`=(LEN(B7:B13)-LEN(SUBSTITUTE(UPPER(B7:B13),UPPER("The"),"")))/LEN("The")`

**Step-3:** Hit the **ENTER** button.

Now youâ€™ve got the count result for the first row in the table.

**Step-4: Drag** the** Fill Handle** icon to the end of the table.

Thatâ€™s it.

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

## Things to Remember

- Select the range of the cells carefully.
- Donâ€™t leave any space inside the double quotation marks.
- Keep the word intended to count inside double quotation marks.

## Conclusion

In this article, we have discussed two methods to count specific words in a column in Excel. The first method serves its purpose concerning the letter case while the second method is blind about the letter cases. Leave any questions that might appear in your mind while going through the article, we will be responding asap.