Re: Excel 2007 - Count of certain Letter within a cell's text string

Hi,

I was reading this post on how to count the number of occurrences of a word in a range of cells and came across an issue. Here's the formula that was recommended, which works well under certain circumstances.

=SUMPRODUCT(LEN(D1:D10)-LEN(SUBSTITUTE(UPPER(D1:D10),UPPER(A1),"")))/LEN(A1)

If I want to search for a single word "a" in a cell with the following content, using a version of this formula (I've attached an example sheet), it returns a value of 5: "This text contains no single character but it does have the letter I'm searching for."

The answer I'm looking for is 0, but as the letter "a" appears 5 times it counts them all. Is there any way to specify it took look for the exact contents of the cell?

Cheers,

3/5