Character Count in Google Sheets Made Easy

Video how to count characters in google sheets

If you work with text data in Google Sheets, you may occasionally need to know the character count in Google Sheets. Luckily, it’s incredibly easy to use Google Sheets formulas to get the character count of cells, ranges, and even specific characters. In this article, 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 called the LEN function, which allows you to count characters in a cell. Let’s say you have the following dataset:

To count the number of characters in each cell, you can use the following formula:
=LEN(B2)

Does a LEN Function Count Spaces?

The LEN function counts every character, whether it’s a letter, number, punctuation mark, space, or symbol. However, keep in mind that you may get an incorrect result due to leading, trailing, or double spaces in a cell. For example, because the LEN function counts all characters, cell B6 shows 27 (instead of 25) characters due to multiple spaces:

Using the TRIM Function to Remove Spaces

To ensure 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. Simply use the formula:
=LEN(TRIM(A2))

How to Count Characters in a Range

You can also count the total number of characters in a range of cells. For example, let’s say you want to count the total number of characters in cells A2:A6:

Use the following formula to achieve this:
=SUMPRODUCT(LEN(A2:A6))

The SUMPRODUCT function provides the sum of all the character counts within the entire range, as 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 need to use Control + Shift + Enter instead of simply hitting “enter.”

Using Array Formula to Count Characters in Range

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

How to Count the Number of Specific Characters in Google Sheets

If you need to count the number of times a specific character (or a string in a cell) shows up, it’s also possible. For example, if you want to know how often the letter “a” has been used in each movie name, use this formula:
=LEN(A2)- LEN(SUBSTITUTE(A2,"a",""))

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

Remember that the SUBSTITUTE function is case-sensitive. If you want it to count both lower and upper-case “a” symbols, use this formula:
=LEN(A2)- LEN(SUBSTITUTE(LOWER(A2),"a",""))

Looking to Improve Your Google Sheets Skills?

Now that you know how to count characters in Google Sheets, you can simplify and streamline your user experience. Continue your learning experience with Mr Reviews‘s comprehensive guide on Google Sheets. You may also want to check out the following Google Sheets tutorials: