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 Excel formula will count the occurrences of text in a cell.
=(LEN(B2)-LEN(SUBSTITUTE(B2,"blue",)))/LEN("blue")
The above first counds the length of the string.
The following file shows an example list. Note the term is case sensitive, Blue is different to blue for the purposes of finding an exact match.
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.
