Count Text in a Cell

In the following example I will show you how to count every instance of the word "blue" which will allow for an exact text match within Excel.  I was recently asked to count the exact match of a text string in a cell.    The following is a link to the thread on Ozgrid.  The following Excel formula will count the occurrences of text in a cell.

=(LEN(B11)-LEN(SUBSTITUTE(B11,"blue",)))/LEN("blue")

It works by isolating the exact match of the word blue in the cell then dividing the length of the characters in the cell which equal blue by the length of the string blue (4 characters).  If there is one instance of blue in the cell then the formula is 4/4=1.  If there are two then the formula is 8/4=2.  

The following Excel file attached shows the above example.