If we can count the total number of characters in a cell using LEN, we can also count specific characters in a cell. We just need a helping hand. And that helping hand is SUBSTITUTE function of excel. Let's start counting.
Generic Formula To Count Specific Characters in Excel - Case sensitive
Generic Formula To Count Specific Characters in a Cell - Case Insensitive
LEN Function returns total number of characters in a string or cell.
SUBSTITUTE function replaces the given character (or set of characters) with another given character (or set of characters). To eliminate character/s we use “” (blank) as a new character. Remember SUBSTITUTE is case sensitive.
Now let's see it in action.
Here, in cell A2, I have a paragraph. Now I want to count words in it. To do so, we will count spaces in the sentence because:
Total Number of Words = Total Number of Spaces +1
Write this formula in Cell B2
Formula To Count Words in A Paragraph
So yeah guys, this is the way you can count any character in a string in excel. Just replace the character/s with blank (“”) and subtract its length from original text.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.