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.
Read more: How to Count Specific Characters in a Column in Excel
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.
Read More: How to Count Alphabet in Excel Sheet
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.
Read More: How to Count Space Before Text in Excel
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- Select the Run option.
- We can see a small Excel message window.
- Type the required specific character “C” in the blank box.
- Click OK.
- The result appears in a window.
Read More: Excel VBA: Count Characters in Cell
Download the Practice Workbook
Related Articles
- How to Count Characters in Cell without Spaces in Excel
- How to Count Characters in Cell Including Spaces in Excel
- How to Count Occurrences of Character in String in Excel
<< Go Back to Count Characters in Cell | String Manipulation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Very helpful page, Nuraida! Thank you so much.
Hello Snootch,
You are most welcome. Thanks for your appreciation and feedback. Glad to hear that our page is helful to you.
Regards,
ExcelDemy