Lesson#137: How to count any character in a text in Excel

Now I will show you to make a formula that can count any specific character or characters in a text. This means if I write a “Banana” then this formula can count how many “a” is in that text.

So what will be the logic behind making some formula? We shall learn how to count any character in a text in Excel.

The formula

This formula will make the difference in text length before and after disappearing the specific character within the text.

Here in cell B2, I have written the text “Be Excellent with Excel” and in cell C2, I have written E (in the capital). Now I have to return the value of how many times Capital E appears in this text.

So I have put the formula in cell D2.

=LEN(B2)-LEN(SUBSTITUTE(B2,C2,””))

I show accurately the result.