Table of Contents
Just like with any other spreadsheet software, the Google Sheets has a function that allows you to count the number of characters in a cell. The =COUNTIF function can be used to count the number of cells that contain a certain character or set of characters.
For example, if you wanted to count the number of cells that contain the letter “a”, you would use the following formula: =COUNTIF(A1:A10,”a”). This would count the number of cells in the range A1:A10 that contain the letter “a”.
You can also use the =COUNTIF function to count the number of cells that contain a certain set of characters. For example, if you wanted to count the number of cells that contain the letters “a” and “b”, you would use the following formula: =COUNTIF(A1:A10,”a,b”). This would count the number of cells in the range A1:A10 that contain the letters “a” and “b”.
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.
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.
The following tutorials explain how to perform other common operations in Google Sheets: