While dealing with MS Excel you might need to count the number of specific characters in a cell in Excel. There are several techniques to perform this task. This tutorial will demonstrate 2 methods to count specific characters in a cell in Excel using formulas.
Download Practice Workbook
2 Methods to Count the Number of Specific Characters in a Cell
The basic formula we are going to use is:
=LEN(text)-LEN(SUBSTITUTE (text, new_text, replace_with))
Before we start, we are giving you a summary of these functions:
This function gives you the length of a text.
You can replace any number of characters of a string with new characters.
Now, we are going to show you both case-sensitive and case-insensitive approaches.
1. Count Specific Characters’ Number in a Cell in Excel: Case-Sensitive Approach
The formula we are using is:
=LEN (text) - LEN(SUBSTITUTE (text, new_text, replace_with))
The formula above will give you the exact count of characters that you want. It will only count case-sensitive values.
Let’s assume that we have the following data set.
We are going to count the number of the characters E, I, and C in the texts in B5, B6, and B7 respectively.
Type the following formula in Cell D5:
Drag the Fill Handle (little box-like shape) over the cells D6 and D7.
Thus we have successfully counted the number of the specific characters E, I, and C in Cells B5, B6, and B7 respectively.
Again, we are going to use the same data set. In this case, we are going to count the lower case characters.
Type the same formula, we used earlier, in Cell D5:
After that, drag the Fill Handle over cells D6 and D7.
Finally, we have counted the number of the specific characters e, i, and c in Cells B5, B6, and B7 respectively.
2. Count Specific Characters’ Number in a Cell in Excel: Case-Insensitive Approach
There may be cases that you have to count a character in a cell. But you don’t need the exact match. You just want to know whether the character is present in the cell or not.
In such a case, we are gonna use a formula similar to the formula we used earlier. We are gonna combine the functions LOWER OR UPPER with that formula.
The UPPER or LOWER function will change the whole text and character into upper or lower case respectively.
First of all, write the formula in cell Count.
Then, press Enter.
You can see the result in the following screenshot.
Drag the Fill Handle over Cell D5 and D6.
Hope you will find all these methods instrumental. The workbook is there for you to download and practice yourself. I definitely hope that it will be helpful for you. Don’t forget to check our website Exceldemy.com for various Excel-related problems and their solutions. If you have any questions, comments, or any kind of feedback, please let me know in the comment box.