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:
….where the “text” is the content in the cell of interest and “word” or “Substring” is the actual word you want to count.
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:
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.
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