Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Formula to Count Occurrence of Specific Characters in a Cell
Formula that counts the occurrences of a specific character in a cell in Excel.
This tutorial includes a way to do a case sensitive and case insensitive count.
Sections:
Case Sensitive Count
=LEN(A2)-LEN(SUBSTITUTE(A2,"i",""))
This formula counts the occurrence of a lower case i in cell A2. To count an upper case, just input the upper case version of the letter.
This is the default way to count a character in a cell and it is case sensitive; if you input a lower-case character, only the lower-case will be counted, and the same for upper case.
How it Works
(If you want, you can skip this part and just copy/paste the above formula and change the cell references to the cell that contains the text.)
The principal behind this formula is that you count how many characters are in a cell and then you perform a second count of the characters in the cell after you remove the character that you want to count, using the SUBSTITUTE function; then you substract the second number by the first one to get the final count.
LEN(A2) counts the characters in cell A2.
SUBSTITUTE(A2,"i","") replaces every instance of the letter i with nothing, an empty space "" and this effectively removes the i from the text. Once that is done, we then need to count how many characters remain after all the i characters have been removed.
LEN(SUBSTITUTE(A2,"i","")) the LEN() that surrounds the SUBSTITUTE function is what performs the second count.
Once both counts have been done, you simply subtract the count without the i from the count of all the characters in the cell and that's how we get the final formula:
LEN(A2)-LEN(SUBSTITUTE(A2,"i",""))
Case Insensitive Count
=LEN(A3)-LEN(SUBSTITUTE(LOWER(A3),"t",""))
This formula counts the occurrence of the letter t in cell A3, including any upper or lower case t characters.
Note: when using this function, you should only input lower-case letters.
How it Works
This works exactly the same as the previous formula with the addition of the LOWER() function.
LOWER(A3) this function changes all the text to lower-case. This allows the SUBSTITUTE() function to work regardless of lower/upper-case text.
To understand the rest of the formula, look to the "How it Works" section for the previous example, as it covers in detail how the formula works.
More Examples
Count Upper Case
=LEN(A4)-LEN(SUBSTITUTE(A4,"T",""))
Count Lower Case
=LEN(A5)-LEN(SUBSTITUTE(A5,"t",""))
Count Spaces
=LEN(A6)-LEN(SUBSTITUTE(A6," ",""))
Notes
Once you understand the concept of how this formula works, you will see that it's not that difficult to use. The one part that is easy to forget is the SUBSTITUTE() function, so just make sure to practice using it.
Make sure to download the attached workbook so you can have all these examples in Excel.
Question? Ask it in our Excel Forum
Tutorial: Formula to count how many times a word appears in a single cell or an entire range in Exce...
Tutorial: Change or replace text in a cell with other text - you can replace a single character, num...
Tutorial: Formulas that allow you to quickly and easily remove the first or last character from a ce...
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the fo...
Tutorial: In Excel you can use a function to capitalize the first letter of every word in a cell. ...
Tutorial: How to count cells that match text at the start or the end of a string in Excel. If you w...