Character Count in Google Sheets Made Easy

Sumit

Last updated August 20, 2023

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.

Table of Contents

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:

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

=LEN(B2)

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:

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.

Use the formula that will do this:

=SUMPRODUCT(LEN(A2:A6))

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”,””))

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: