Count specific words in a cell

If you have a text in a cell and you would wish to test how many times a certain word appears in that cell, use a formula based on LEN and SUBSTITUTION. It is a straightforward formula to apply.

Here’s how the formula looks:

Generic Formula

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

….where the “text” is the content in the cell of interest and “word” or “Substring” is the actual word you want to count.

Application Example

We are going to look at a couple of text in different cells and count how many times a particular word appears in the individual cells. Check out how we have solved the problem in this example.

Figure 1. Example 1 of LEN/SUBSTITUTE Functions

From the screenshot, the formula in the highlighted cell (D6) is:

=(LEN(B6)-LEN(SUBSTITUTE(B6,C6,"")))/LEN(C6)

Here’s How the Formula Works

From the example, we are counting the word “coming” in cell B6. The working starts from the inside. SUBSTITUTE eliminates the substring from the original text in B6. LEN then count the length of the text without substring. That gives a number that is subtracted from the original text’s length to generate results of the removed characters in substring in the initial stage.

The number of the missing characters is then divided by substring length. For instance, if you have three missing 12 and your substring gas 6 characters, then it is an indication that it has appeared twice. That the solution for B6 which is in D6.

Case Sensitivity

The function that we have used for D6 is case sensitive. That is the reason why D9 result is zero. However, you can have an UPPER function inside the SUBSTITUTE for the text to be converted to upper case before substituting. Here’s the formula that has been used for D10 to correct D9.

Figure 2. Example 2 of LEN/SUBSTITUTE/UPPER Functions

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar