Counting Specific Characters in Excel (Excel Formula)

Counting Specific Characters in Excel (Excel Formula). To count the occurrence of a specific character in Excel, you can use the combination of SUBSTITUTE and LEN functions. The LEN function counts the total characters, and the SUBSTITUTE function removes the character you want to count from the main value.

Counting Specific Characters in Excel (Excel Formula)

Then, you can count the total characters again and compare the counts to get the occurrence of the specific character. Here’s an example:

Suppose you have a long sentence in cell A1, and you want to count how many times the letter “e” occurs. To do this, you can use the following formula in cell B1:

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

This formula subtracts the length of the original sentence with the length of the sentence after removing all “e” characters. The result is the count of “e” characters in the sentence.

To verify the formula, you can run a loop through all the characters in cell A1 using VBA:

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

The formula works by first counting the total number of characters in cell A1 using the LEN function. Then, the SUBSTITUTE function replaces all “e” characters in the sentence with a blank character, and the LEN function is wrapped around this result to count the total characters without the “e” characters. Finally, the count of “e” characters is obtained by subtracting the second count from the first count.