Excel Count Specific Characters in Cell (4 Quick Ways)

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.


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.

Use of LEN & SUBSTITUTE Functions to Count Specific Character in Excel Cell

STEPS:

  • First, select Cell D5.
  • Then type the formula:
=LEN(C5)-LEN(SUBSTITUTE(C5,"C",""))

Use of LEN & SUBSTITUTE Functions to Count Specific Character in Excel Cell

  • 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”,””)

This 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”,””))

Here this will subtract the number of characters without the letter “C” from the total number of the characters.

Read more: Count the Number of Specific Characters in a Cell in Excel


2. Counting Specific Character for Upper or Lower Case Occurrence in Excel Cell

Sometimes we may have a dataset with a combination of uppercase and lowercase letters. 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.

Upper Case Occurrence

STEPS:

  • First, select Cell D5.
  • Next type formula:
=LEN(C5)-LEN(SUBSTITUTE(UPPER(C5),"C",""))

Upper Case Occurrence

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”,””))

2.2  Lower Case Occurrence

Let’s say we have a PRODUCT worksheet with a combination of uppercase & lowercase letters.

Lower Case Occurrence

STEPS:

  • At the beginning select Cell D5.
  • Write the formula:
=LEN(C5)-LEN(SUBSTITUTE(LOWER(C5),"c",""))

Lower Case Occurrence

  • 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”,””))

Here 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.


Further Readings:


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.

Use of Excel SUMPRODUCT, LEN & SUBSTITUTE Functions to Count Specific Characters in Cell

STEPS:

  • Select Cell E5.
  • After that, type the formula:
=SUMPRODUCT(LEN(C5:C8)-LEN(SUBSTITUTE(C5:C8,"C","")))

Use of Excel SUMPRODUCT, LEN & SUBSTITUTE Functions to Count Specific Characters in Cell

  • 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”,””))

This will count the number of characters without the letter C in range C5:C8.

LEN(C5:C8)

This will count all the characters in range C5:C8.

LEN(C5:C8)-LEN(SUBSTITUTE(C5:C8,”C”,””))

Here 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.

Apply VBA to Count Specific Characters in an Excel Cell

STEPS:

  • Select the worksheet from the sheet tab at first.
  • Next, right-click on the mouse.
  • Select View code.

Apply VBA to Count Specific Characters in an Excel Cell

  • A VBA Module window pops up.
  • 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.

Apply VBA to Count Specific Characters in an Excel Cell

  • We can see a small Excel message window.
  • Type the required specific character “C” in the blank box.
  • Then click OK.

Apply VBA to Count Specific Characters in an Excel Cell

  • Finally, the result appears in a window.


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.


Related Readings

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. Here I will post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo