How to Count Specific Characters (Formula in Excel)

In Excel, to count a specific character, you need to use a combination of SUBSTITUTE and LEN functions.

LEN counts the total characters and then the Substitute function removes the character that you want to count from the main value.

After that, you can again count the total characters and compare them with the original count to get the count of that specific character.

In the following example, you have a long sentence in cell A1, and now, from the sentence, you need to count how many times the letter “e” occurred. We will write this formula in cell B1.

You can use the following steps to write this formula:

1. First, in cell B1, enter the LEN function and refer to cell A1.
2. After that, enter a minus sign and enter the LEN function again.
3. Next, you need to enter the SUBSTITUTE function within the LEN function.
4. Now, enter the substitute function and refer to cell A1 again.
5. From here, you need to specify the character you want to replace in the second argument
6. Then, there is a blank character in the third argument of the SUBSTITUTE function.
7. In the end, hit enter the closing parentheses and hit enter.

The moment you hit enter, it returns the count character “e” in the result, which is twenty-four.

=LEN(A1)-LEN(SUBSTITUTE(A1,"e",""))

To verify this formula, I used the following code to run a loop through all the characters in cell A1.

Sub LoopString()

Dim Counter As Integer
Dim MyString As String
Dim i As Integer

MyString = Range("A1").Value

For Counter = 1 To Len(MyString)
If Mid(MyString, Counter, 1) = "e" Then
i = i + 1
End If
Next

MsgBox i

End Sub

Read Also – Count Cells by Color in Excel

How this formula works

Now it is time to understand how this formula works, and for this, we need to split this formula into three parts.

1. In the first part, the LEN function counts the total number of characters from cell A1 and returns 199 in the result.
2. In the second part, we used a substitute function to replace “e” with a no value.
3. In the third part, we have rapped substitute function to count the characters from the for value where character “e” is not there.