Excel Formulas to Count specific word in a cell ~ Quick Tricks!!

For instance, you have a list of text strings in a range, and you need to count the specific words in a cell. Do you have any good methods for solving this task in Excel? In this post, we are going to discuss the formulas to count the specific word in a cell in Excel. Let’s step into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

General Formula:

You can use the below formula to count how many times a specific word (or any substring) appears inside a cell in Excel.

=(LEN(text)-LEN(SUBSTITUTE(text,word,””)))/LEN(word)

Syntax Explanations:

LEN – In Excel, the LEN function is used to find the length of the text string.
SUBSTITUTE – This function helps to replace existing text with new text in a text string when you want to replace text based on its content, not position. Read more on the SUBSTITUTE function.
Text – It represents the input value.
Division (/) – It is used for dividing values or numbers.
Comma symbol (,) – It is a separator that helps to separate a list of values.
Parenthesis () – The main purpose of this symbol is to group the elements.
Minus Operator (-) – This symbol will help to subtract any two values.

Example:

Refer to the below example image.

First, we will enter the input text strings in Column B and Column C.
Here, we are going to count the number of a specific word inside the cell.

Select any cell and type the above-given formula.

Finally, press the ** ENTER** key to get the result out, if you need, drag the fill handle over range to apply the formula.

Wrap-Up:

So yeah guys, this is how we can count the number of times a specific word (or any substring) appears inside a cell in Excel. Hope you like it. If you have any other ideas or questions , share them with us here in the comments section below.