You can use the following formulas in Excel to count specific characters in a column:
Formula 1: Count Specific Characters in One Cell
=LEN(A2)-LEN(SUBSTITUTE(A2,"r",""))
This particular formula counts the number of characters equal to “r” in cell A2.
Formula 2: Count Specific Characters in Entire Range
=SUMPRODUCT(LEN(A2:A11)-LEN(SUBSTITUTE(A2:A11,"r","")))
This particular formula counts the number of characters equal to “r” in the entire range A2:A11.
The following examples show how to use each formula in practice with the following list of basketball team names in Excel:
Let’s jump in!
Example 1: Count Specific Characters in One Cell
We can type the following formula into cell B2 to count the number of characters equal to “r” in cell A2:
=LEN(A2)-LEN(SUBSTITUTE(A2,"r",""))
We can then click and drag this formula down to each remaining cell in column B:
Column B now displays the total number of characters in the corresponding cell in column A that ar equal to “r”.
For example:
- Mavs contains 0 characters equal to “r”
- Spurs contains 1 character equal to “r”
- Rockets contains 0 characters equal to “r”
And so on.
Note: This formula is case-sensitive.
Example 2: Count Specific Characters in Entire Range
We can type the following formula into cell D1 to count the number of characters equal to “r” in the entire range A2:A11:
=SUMPRODUCT(LEN(A2:A11)-LEN(SUBSTITUTE(A2:A11,"r","")))
The following screenshot shows how to use this formula in practice:
The formula tells us that there are a total of 7 characters equal to “r” among all cells in the range A2:A11.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Count Frequency of Text in Excel
How to Check if Cell Contains Text from List in Excel
How to Calculate Average If Cell Contains Text in Excel
