To count a specific character in Google Sheets we can use combination formulas.

The specific character can be alphabets (case sensitive or case insensitive), numbers, special characters such as a pipe, tide, white space, newline, etc.

I have two types of combinations (formulas) for this. One is using regular expressions and the other one is without using any regular expression.

But there is one thing in common that both of the formulas use the Len function. The good thing is that the latter one will work in Excel too.

You May Like: Google Sheets Functions Guide.

So if you are a Google Sheets or Excel user, you can use my formula to count specific characters in your Spreadsheet.

Why should one count a specific character in a Spreadsheet?

It depends. For me, I’ll use it to replace a specific occurrence of a character in a string.

Do you want an explanation?

For example, if a comma separator appears 10 times in a string, I can substitute the fifth occurrence of that separator with another delimiter (separator) to split the string at that specific point. I’ll explain it in another tutorial.

Count a Specific Character in a Cell in Google Sheets

Let’s start with the first combination formula, i.e. the Substitute and Len, as it will work in both of the two popular Spreadsheet applications – Excel and Google Sheets.

Using Substitute and Len

To count the total number of characters in a string there is a function called LEN in Google Sheets/Excel. Experts normally use it in formulas to exclude blank cells.

Tip:

Assume cell C5 contains the string “Info Inspired”. The formula =len(C5) would return 13 which is the count of the number of characters in cell C5.

Now try this ISBLANK formula (it’s not part of our combination formula that counts a specific character in Google Sheets).

=isblank(C5)

Using LEN we can replace it as below.

=if(len(C5),FALSE,TRUE)

Back to our topic and let’s see how to use Substitute and Len combo to count a specific character in Google Sheets.

Cell A2 contains the following string.

May | June | July | August | September

I want to count the number of | which is the separator in the said string. Here are the steps.

Using following Len we can get the total number of characters in cell A2. Here it would be 38.

=len(A2)

Now what we want to do is Substitute the character | with null and get the length of characters using the following formula.

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

It would return 34. So the count of the said character will be 38-34=4. We can write the combination formula as below.

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

Using Regex and Len

If we can use Substitute to replace the specific character to count, why can’t using Regexreplace?

We can do that! But here we will follow a different approach.

Instead of replacing the specific character, we will replace all the other characters in the string and count the balance characters using Len.

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

Note: This formula won’t work in Excel.

Both the above combination formulas are case sensitive. To make them case insensitive, use the following formulas.

Case Insensitive Formulas to Count a Specific Character:

Count the letter “a” (both lower and upper) in the string ababAbAaaa.

In Len and Substitute combination, we can make the cell value to lower case and use lower case character to substitute.

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

In the Len and Regexreplace combination, we can instead use the regular expression (?i) as below.

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

Count a Specific Character in a Range of Cells in Google Sheets

We were discussing counting the number of characters as well as a specific character in a single cell or we can say in a string in a cell. What about a cell-range?

Since all the functions we have used (Len, Substitute, Regerepalce, and Lower) support Google Sheets ArrayFormula function, we can make the two combinations as array formulas.

In addition to this, we must use the Sum function. Why?

There are a few fruit names in the range A2:B4. I want to count the total number of the character “a” in this range.

Formula # 1:

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

Formula # 2:

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

The Len function in both the formulas return the length in a range. So using Sum we can get the total length.

The above formulas are case sensitive. Make them case insensitive as per my non-array formulas.

Conclusion

Here are some ready to use formulas for you.

Count the Total Number of Space Character 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]",""))

You can use these formulas as array formulas too. That’s all about how to count a specific character in Google Sheets. Enjoy!