Nuraida Kashmin
Sometimes we need to count only the specific characters in an Excel cell. Different excel functions make this easy for us. In the article, we are going to learn some of them with examples and explanations.
Download Practice Workbook
Download the following workbook and exercise.
4 Quick Ways to Count Specific Characters in an Excel Cell
1. Use of Excel LEN & SUBSTITUTE Functions to Count Specific Character in Cell
SUBSTITUTE function is a case-sensitive function. To replace one or more characters with another we use this function. On the other hand, the LEN function counts the cell characters. Assuming we have a PRODUCT worksheet. We are going to count the specific character “C” in the cell.
STEPS:
- First, select Cell D5.
- Then type the formula:
=LEN(C5)-LEN(SUBSTITUTE(C5,"C",""))
- Next hit Enter to see how the formula actually worked. We can see the subtracted number here, which is actually the count of the required specific character.
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” into space in Cell C5. - LEN(SUBSTITUTE(C5,”C”,””))
Now this will count the number of characters without the letter C in Cell C5. - LEN(C5)-LEN(SUBSTITUTE(C5,”C”,””))
Finally, this will subtract the number of characters without the letter “C” from the total number of characters.
Read more: How to Count Number of Characters in a Cell in Excel (Easiest 6 Ways)
2. Counting Specific Characters for Upper or Lower Case Occurrence in Excel Cell
Sometimes we may have a dataset with a combination of uppercase and lowercase letters. So, for this type of case-insensitive character count, we need to use the UPPER function or the LOWER function inside the SUBSTITUTE function.
2.1 Upper Case Occurrence
Here we have a PRODUCT dataset.
STEPS:
- First, select Cell D5.
- Next type formula:
=LEN(C5)-LEN(SUBSTITUTE(UPPER(C5),"C",""))
Finally, this will subtract the number of characters without the letter “C” from the total number of characters.
- At last, 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”,””)
Now this will replace all the letters “C” into space in Cell C5. - LEN(SUBSTITUTE(UPPER(C5),”C”,””))
Here this will count the number of characters without the letter C in Cell C5. - LEN(C5)-LEN(SUBSTITUTE(UPPER(C5),”C”,””))
Finally will give the upper-case occurrence.
2.2 Lower Case Occurrence
Let’s say we have a PRODUCT worksheet with a combination of uppercase & lowercase letters.
STEPS:
- At the beginning select Cell D5.
- Write the formula:
=LEN(C5)-LEN(SUBSTITUTE(LOWER(C5),"c",""))
- Lastly, 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 space. - LEN(SUBSTITUTE(LOWER(C5),”c”,””))
Again this will count the number of characters without the letter c in Cell C5. - LEN(C5)-LEN(SUBSTITUTE(LOWER(C5),”c”,””)
In the end, this will subtract the number of characters without the letter “c” from the total number of characters.
Read More: Count Numbers in a Cell in Excel (3 Methods)
3. Use of Excel SUMPRODUCT, LEN & SUBSTITUTE Functions to Count Specific Characters in Cell
Sometimes we need to count the specific characters from the whole range to save time. For that, we use the SUMPRODUCT function as it returns the total count of the data and their sum from a specific array. Imagine we have a PRODUCT worksheet. We are going to count the total number of a specific character “C” from the range C5:C8.
STEPS:
- Select Cell E5.
- After that, type 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 space in range C5:C8. - LEN(SUBSTITUTE(C5:C8,”C”,””))
Next, this will count the number of characters without the letter C in range C5:C8. - LEN(C5:C8)
Further, this will count all the characters in range C5:C8. - LEN(C5:C8)-LEN(SUBSTITUTE(C5:C8,”C”,””))
Lastly, this will subtract the number of characters without the letter “C” from the total number of the characters. - SUMPRODUCT(LEN(C5:C8)-LEN(SUBSTITUTE(C5:C8,”C”,””)))
In the end, this will sum up the total array and return the result.
4. Apply VBA to Count Specific Characters in an Excel Cell
By applying the VBA code, we can quickly count the number of specific characters. Here we have to count the character “C” from the below dataset.
STEPS:
- First, select the worksheet from the sheet tab at first.
- Next, right-click on the mouse.
- Again, select View code.
- As a result, a VBA Module window pops up.
- Again, on the window, type the below code.
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- Then select the Run option.
- As a result, we can see a small Excel message window.
- Now, type the required specific character “C” in the blank box.
- Then click OK.
- Finally, the result appears in a window.
Read More: Excel VBA: Count Characters in Cell (5 Methods)
Conclusion
These are the quickest ways to count specific characters in an Excel Cell. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods in the comment section.
