How to Count Specific Words in a Column in Excel (2 Methods)

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.


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")

excel count specific words in column using formula

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.

count specific words in column fill handle

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


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")

count specific words in a column in excel ignoring the case

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.

count words in excel ignoring case fill handle

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.


Related Readings

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo