Mehrab Imtiaz
Knowing how to perform a character count in Google Sheets can often come in handy in many situations. And today we will look at some of these scenarios and discuss the best approaches to them.
Let’s get started.
How to Perform Character Count in Google Sheets
A better part of our article relies on the use of the LEN function. The purpose of this function aligns with what we are looking for in this article, which is counting characters in Google Sheets.
The LEN function syntax:
LEN(text)
We simply input the text or string that we want to count the characters of, or simply input the cell reference of the cell that contains our text.
The LEN function in action:
1. Count Characters from a Single Cell in Google Sheets
Continuing from our explanation of the LEN function, we now have a list of Movie names that we want to count the characters:
Now, we simply input the LEN function with the cell reference of the movie in each row:
=LEN(B3)
However, the function is not infallible. The LEN function also considers any leading, trailing, or extra spaces in between the strings.
To overcome this, we can include the TRIM function within LEN to remove any whitespaces from our count:
=LEN(TRIM(B3))
2. Count Characters from a Range of Cells
We can also use the LEN function to count all the characters in a range of cells. Though LEN can’t accomplish this on its own. We must take the assistance of another fundamental function of Google Sheets for this: the SUMPRODUCT function.
Our formula:
=SUMPRODUCT(LEN(B3:B8))
The idea is to count the number of characters in each cell in the range with LEN and add them all together with the SUMPRODUCT function.
You can alternatively use the SUM function instead of SUMPRODUCT. But since SUM can’t take an array of data, you will have to utilize the ARRAYFORMULA function to make it work.
Character count over a range in Google Sheets using SUM:
=ArrayFormula(SUM(LEN(B3:B8)))
Tip 1: You can automatically apply the ARRAYFORMULA around your formula by simply typing the formula, =SUM(LEN(B3:B8)), and pressing CTRL+SHIFT+ENTER instead of just ENTER.
Tip 2: As we have seen in the previous section, you can also apply the TRIM function within LEN to remove any extra whitespaces.
3. Count Specific Characters in Google Sheets
Our final section discusses how to character count specific characters from a string in Google Sheets. We once again take the help of the LEN function, but this time, we will also utilize the SUBSTITUTE function.
The SUBSTITUTE function syntax:
SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
Now, let’s say we want to count all the occurrences of the character “a” in a cell. Our formula:
=LEN(B3)-LEN(SUBSTITUTE(B3,"a",""))
As you can see, the cell containing “Apple” does not return any value for the count of “a”. This is because our formula is case-sensitive.
You can obviously keep the formula unchanged if it’s the case-sensitive outcome you require. However, we can make this formula count any character case by adding the LOWER function to our formula.
=LEN(B3)-LEN(SUBSTITUTE(LOWER(B3),"a",""))
As you can see, the “A” in “Apple” and “Avocado” has been counted.
Final Words
That concludes all the ways we can character count in Google Sheets. We hope that this guide has covered enough approaches to help you better understand all the ways we can count characters in google sheets.
Please feel free to leave any queries or advice you might have for us in the comments section below.
