How to fix the bugs in excel

Question

To understand the formula's logic, let's break it down into smaller parts:

First, you find the total string length in cell A3 with:

LEN(A3)

Then, you remove all occurrences of the letter "A" in A3 by replacing it with an empty string:

SUBSTITUTE(A3, "A", "")

The next step is to find the string length without the letter "A":

LEN(SUBSTITUTE(A3, "A", ""))

Finally, you subtract the length of the string without "A" from the total length string:

LEN(A3) - LEN(SUBSTITUTE(A3, B3, ""))

As the result, you get the count of "removed" characters, which is the number of occurrences of that particular character in the cell.

Case-insensitive formula to count letters in Excel cell

When counting letters in Excel cells, you may sometimes need a formula that ignores the letter case. To make such a formula, use the UPPER function inside SUBSTITUTE to convert a given letter to uppercase before running the substitution.

For example, to count both "A" and "a" in cell A3, use this formula:

=LEN(A3) - LEN(SUBSTITUTE(UPPER(A3), "A", ""))

The LOWER function will also do:

=LEN(A3) - LEN(SUBSTITUTE(LOWER(A3), "a", ""))

A slightly more complex way is using nested SUBSTITUTE functions:

=LEN(A3) - LEN(SUBSTITUTE(SUBSTITUTE (A3, "A", ""), "a", "")

In our data set, the letters to be counted are input in column B, so we convert both the source cell and the cell containing the character to uppercase:

=LEN(A3) - LEN(SUBSTITUTE(UPPER(A3), UPPER(B3),""))

And this works beautifully irrespective of the target letter's case:

This question is related to the following Learning Module