Character Count in Google Sheets Made Easy

If you work with text data in Google Sheets, you may occasionally need to know the character count in Google Sheets.

It turns out that it’s extremely easy to use Google Sheets formulas to get the character count of cells, ranges, and even specific characters.

Here, I’ll show you how to count the number of characters using Google Sheets’ LEN function.

Determining Character Count in Google Sheets

Google Sheets has a dedicated function to count characters in a cell: The LEN function.

Suppose you have the following dataset:

example of counting characters in Google Sheets

To count the number of characters in each cell, use the following formula:

=LEN(B2)

Count Characters using LEN function

Related: Counting Non-Empty Cells in Google Sheets

Does a LEN Function Count Spaces?

Whether it’s a letter, number, punctuation mark, space, or symbol, the LEN function counts every character. Therefore, it’s important to consider that you may receive an incorrect result due to leading, trailing, or double spaces in a cell.

Because the LEN function counts all characters, cell B6 shows 27 (instead of 25) characters. That’s because there are multiple spaces:

Wrong LEN function result because of spaces

Using the TRIM Function to Remove Spaces

To make sure you’re not counting extra spaces, you can use the TRIM function along with the LEN function. This will ignore any leading, trailing, or double spaces in Google Sheets:

=LEN(TRIM(A2))

How to Count Characters in a Range

It’s also possible to count the total number of characters in a range of cells. Using the following dataset, suppose you want to count the total number of characters in cells A2:A6.

Data to count range characters in Google Sheets

Use the formula that will do this:

=SUMPRODUCT(LEN(A2:A6))

Count all characters in sheets range


SUMPRODUCT is used to provide the sum of all the character counts within an entire range (carried out by the LEN function).

Using SUM Function to Count Characters in Range

You can also accomplish the same results with a SUM function:
=SUM(LEN(A2:A6))

However, since the SUM function can’t handle arrays, you’ll have to use Control + Shift + Enter instead of simply hitting “enter.”

Using Array Formula to Count Characters in Range

To sidestep the restrictions of SUM function, use the following formula:
=ArrayFormula(SUM(LEN(A2:A6)))

How to Count the Number of Specific Characters in Google Sheets

You can also count the number of times a specific character (or a string in a cell) shows up.
Suppose you want to know how often the letter “a” has been used in each movie name. You can accomplish this by using the following formula:
=LEN(A2)- LEN(SUBSTITUTE(A2,”a”,””))

Count number of times character used Google Sheets

Related: How to Count Cells with Specific Text in Google Sheets

How to Count Lower- and Upper-Case Letters in Google Sheets

It’s important to remember that the SUBSTITUTE function is case-sensitive. If you want it to count both lower and upper case “a” symbols, use the following formula:
=LEN(A2)- LEN(SUBSTITUTE(LOWER(A2),”a”,””))

Looking to Improve Your Google Sheets Skills?

Once you know about character count in Google Sheets, you’ll simplify and streamline your user experience. Continue your learning experience with one of Coursera’s hundreds of courses, degrees, and certificates!

You may also want to check out the following Google Sheets tutorials:

Most Popular Posts

2 thoughts on “Character Count in Google Sheets Made Easy”

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!