You can use the following formulas to count the number of specific characters in cells in Google Sheets:
Formula 1: Count Specific Characters (Case-Sensitive)
=LEN(A2)-LEN(SUBSTITUTE(A2,"a",""))
This particular formula counts all occurrences of the character “a” in cell A2.
This formula is case-sensitive, so any “A” characters in cell A2 will not be counted.
Formula 2: Count Specific Characters (Case-Insensitive)
=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"a",""))
This particular formula counts all occurrences of the character “a” in cell A2.
This formula is case-insensitive, so any “A” characters in cell A2 will also be counted.
The following examples show how to use each formula in practice with the following list of names in Google Sheets:
Example 1: Count Specific Characters (Case-Sensitive)
We can type the following formula into cell B2 to count the number of times the character “a” occurs in cell A2:
=LEN(A2)-LEN(SUBSTITUTE(A2,"a",""))
We can then click and drag this formula down to each remaining cell in column B:
Column B now shows the number of times the character “a” occurs in each corresponding cell in column A.
Notice that the formula returns 0 for the name Andy because this formula is case-sensitive so “A” is not counted.
Example 2: Count Specific Characters (Case-Insensitive)
We can type the following formula into cell B2 to count the number of times the character “a” occurs in cell A2:
=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"a",""))
We can then click and drag this formula down to each remaining cell in column B:
Column B now shows the number of times the character “a” occurs in each corresponding cell in column A.
Notice that the formula returns 1 for the name Andy because this formula is case-insensitive so “A” is counted.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets:
How to Extract Substring in Google Sheets
How to Reverse a Text String in Google Sheets
How to Extract Numbers from String in Google Sheets
