Count Text in a Cell


Counting text inside a cell is achievable with Excel when it appears multiple times. We need to get a little creative though.In the following example we will will 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")

Count occurance of text excel

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.