How to Count Specific Characters in a Cell in Excel (4 Quick Ways)

Method 1 – Use the Excel LEN and SUBSTITUTE Functions to Count Specific Characters in a Cell

We are going to count the specific character “C” in the cells from column C.

STEPS:

Select Cell D5.
Insert the formula:

=LEN(C5)-LEN(SUBSTITUTE(C5,"C",""))

Hit Enter to see how the formula actually worked.
Drag down the Fill Handle.

Formula Breakdown:

LEN(C5)
This will count the number of characters in Cell C5.
SUBSTITUTE(C5,”C”,””)
Then it will replace all the letters “C” with a blank in Cell C5.
LEN(SUBSTITUTE(C5,”C”,””))
This will count the number of characters from the substitution.
LEN(C5)-LEN(SUBSTITUTE(C5,”C”,””))
This will subtract the number of characters in the substitution from the total number of characters.

Method 2 – Counting Specific Characters in Upper or Lower Case in Excel Cell

Case 2.1 – Upper Case Occurrence

We have the same PRODUCT dataset.

STEPS:

Select Cell D5.
Use the following formula:

=LEN(C5)-LEN(SUBSTITUTE(UPPER(C5),"C",""))

Hit Enter to see the result.

Formula Breakdown:

UPPER(C5)
This will convert all letters of a text string (Cell C5) into uppercase.
SUBSTITUTE(UPPER(C5),”C”,””)
This will replace all the letters “C” into a blank in Cell C5.
LEN(SUBSTITUTE(UPPER(C5),”C”,””))
This will count the number of characters without the letter C in Cell C5.
LEN(C5)-LEN(SUBSTITUTE(UPPER(C5),”C”,””))
This will give the result.

Case 2.2 – Lower Case Occurrence

We have a PRODUCT worksheet with a combination of uppercase and lowercase letters.

STEPS:

Select Cell D5.
Use the formula:

=LEN(C5)-LEN(SUBSTITUTE(LOWER(C5),"c",""))

Press Enter.

Formula Breakdown:

LEN(C5)
This will count all the characters in Cell C5.
LOWER(C5)
This will convert all letters of a text string (Cell C5) into lowercase.
SUBSTITUTE(LOWER(C5),”c”,””)
Now in Cell C5, this will replace all the letters “c” into a blank.
LEN(SUBSTITUTE(LOWER(C5),”c”,””))
This will count the number of characters without the letter c in Cell C5.
LEN(C5)-LEN(SUBSTITUTE(LOWER(C5),”c”,””)
This will subtract the number of characters without the letter “c” from the total number of characters.

Method 3 – Use of Excel SUMPRODUCT, LEN, and SUBSTITUTE Functions to Count Specific Characters in a Cell

We are going to count the total number of a specific character “C” from the range C5:C8.

STEPS:

Select Cell E5.
Use the formula:

=SUMPRODUCT(LEN(C5:C8)-LEN(SUBSTITUTE(C5:C8,"C","")))

Hit Enter to see the result.

Formula Breakdown:

SUBSTITUTE(C5:C8,”C”,””)
This will replace all the letters “C” into a blank in the range C5:C8.
LEN(SUBSTITUTE(C5:C8,”C”,””))
This will count the number of characters without the letter C in the range C5:C8.
LEN(C5:C8)
This will count all the characters in the range C5:C8.
LEN(C5:C8)-LEN(SUBSTITUTE(C5:C8,”C”,””))
This will subtract the number of characters without the letter “C” from the total number of characters.
SUMPRODUCT(LEN(C5:C8)-LEN(SUBSTITUTE(C5:C8,”C”,””)))
This will sum up the total array and return the result.

Method 4 – Apply VBA to Count Specific Characters in an Excel Cell

We have to count the character “C” from the below dataset.

STEPS:

Right-click on the worksheet name.
Select View code.

A VBA Module window pops up.
Insert this code in the module.

Option Explicit
Dim x As Integer
Dim a As String
Dim b As Object
Dim y As Integer
Sub a_x()
x = 0
a = InputBox("character(s) to find?")
If a = "" Then GoTo Done
For Each b In Selection
y = InStr(1, b.Value, a)
While y <> 0
x = x + 1
y = InStr(y + 1, b.Value, a)
Wend
Next b
MsgBox x & " instance of " & a
Done:
End Sub