Character Count in Google Sheets (An Easy Guide)

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 function syntax for character count in google sheets

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:

the len function in action animated

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:

dataset to count characters in google sheets

Now, we simply input the LEN function with the cell reference of the movie in each row:


using len function for character count in google sheets

However, the function is not infallible. The LEN function also considers any leading, trailing, or extra spaces in between the strings.

extra spaces will be counted as characters

To overcome this, we can include the TRIM function within LEN to remove any whitespaces from our count:


removing white spaces from our character count using trim function


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:


character count over a range in google sheets using a combination of sumproduct and len

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:


alternative character count over range using sum and arrayformula

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])

substitute function syntax

Now, let’s say we want to count all the occurrences of the character “a” in a cell. Our formula:


count character occurrence in google sheets using len and substitute functions

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.


non case sensitive formula to count character occurrences

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.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply