Count a Specific Character in Google Sheets

GiGiKing·March 23, 2024·0 comments

Are you tired of manually counting specific characters in your Google Sheets? Well, fret not! In this article, I’ll reveal some ingenious formulas that will make counting a breeze. Whether you’re a Google Sheets aficionado or an Excel enthusiast, these formulas will seamlessly work wonders in both applications.

Utilizing Substitute and Len

Let’s dive right into a combination formula that harnesses the power of the SUBSTITUTE and LEN functions. This formula is compatible with both Google Sheets and Excel, making it incredibly versatile and convenient.

To count the total number of characters in a string, we can rely on the LEN function. For instance, if cell C5 contains the string “Info Inspired,” the formula =len(C5) would return 13, which represents the character count in cell C5.

To count a specific character, we need to substitute that character with a null value and then determine the length of the resulting string. Here’s the formula that accomplishes just that:

=LEN(A2) - LEN(SUBSTITUTE(A2,"|",""))

In the above formula, we’re counting the number of pipe characters (|) in cell A2. By substituting the pipe character with a null value and calculating the length of the resulting string, we can accurately determine the count of the specific character.

The Power of Regex and Len

If you’re already familiar with the SUBSTITUTE function, you might be wondering if we can leverage the REGEXREPLACE function to achieve the same result. The answer is a resounding yes! However, we’ll take a slightly different approach.

Instead of replacing the specific character, we will substitute all other characters in the string and then calculate the length of the remaining characters using the LEN function. Here’s the formula in action:

=len(regexreplace(A2,"[^|]",""))

The above formula counts the number of pipe characters (|) in cell A2. By replacing all characters in the string except the pipe character and calculating the length of the resulting string, we can effortlessly determine the count of the specific character. It’s important to note that this formula is specific to Google Sheets and won’t work in Excel.

Both of the aforementioned combination formulas are case-sensitive. However, if you prefer a case-insensitive count, you can easily modify the formulas as follows:

=LEN(A2) - LEN(SUBSTITUTE(LOWER(A2), "a", ""))

=len(regexreplace(A2,"(?i)[^a]",""))

Counting Made Easy with a Range of Cells

So far, we’ve covered counting the number of characters and a specific character in a single cell. But what if you want to count across a range of cells? No worries, I’ve got your back!

Since the functions we’ve used (LEN, SUBSTITUTE, REGEXREPLACE, and LOWER) support the ARRAYFORMULA function in Google Sheets, we can transform the two combinations into array formulas. Additionally, we’ll employ the SUM function to calculate the total count.

Let’s say we have a range of fruit names in cells A2:B4, and we want to count the total number of the character “a” in this range. Here are the formulas that will do the trick:

Formula #1:

=ArrayFormula(SUM(LEN(A2:B4))-SUM(LEN(SUBSTITUTE(A2:B4,"a",""))))

Formula #2:

=ArrayFormula(SUM(len(regexreplace(A2:B4,"[^a]",""))))

By using the LEN function in both formulas to determine the length of the range, we can then utilize SUM to calculate the total length. Keep in mind that these formulas are also case-sensitive. To make them case-insensitive, refer to the modifications mentioned earlier.

In Conclusion

To wrap it all up, here are some ready-to-use formulas just for you:

Count the Total Number of Space Characters in a Cell:
- Formula #1: =LEN(A2) - LEN(SUBSTITUTE(A2, " ", ""))
- Formula #2: =len(regexreplace(A2, "[^s]", ""))
Count the Total Number of New Lines in a Cell:
- Formula #1: =LEN(A3) - LEN(SUBSTITUTE(A3, CHAR(10), ""))
- Formula #2: =len(regexreplace(A3, "[^n]", ""))