Formula to Count Occurrence of Specific Characters in a Cell

Add to Favorites
Author: don | Edits: don

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.

442918f1a0611c5b586c10a2e1a37b0d.png

Sections:

Case Insensitive Count

Case Sensitive Count

More Examples

Notes

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

56faec0a755deb50d291d35e05c15024.png

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.

8b7b8d720014809b3992599e5400c115.png

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

12e8111e3e84dd334d970f0cdce98aa1.png

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


Excel Function: LEN, LENB(), LOWER(), SUBSTITUTE()
Downloadable Files: Excel File

Similar Content on TeachExcel
Formula to Count Occurrences of a Word in a Cell or Range in Excel
Tutorial: Formula to count how many times a word appears in a single cell or an entire range in Exce...
Change Specific Text within a Cell in Excel
Tutorial: Change or replace text in a cell with other text - you can replace a single character, num...
Formulas to Remove First or Last Character from a Cell in Excel
Tutorial: Formulas that allow you to quickly and easily remove the first or last character from a ce...
Formula to Delete the First or Last Word from a Cell in Excel
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the for...
Capitalize First Letter of Every Word in a Cell - PROPER Function
Tutorial: In Excel you can use a function to capitalize the first letter of every word in a cell.  T...
Count the Number of Cells that Start or End with Specific Text in Excel
Tutorial: How to count cells that match text at the start or the end of a string in Excel. If you w...