How to count characters in a cell in Google Sheets

Unlike Google Docs, Google Sheets doesn’t automatically keep track of character counts for you. If you need to count all characters or specific characters in a cell, there are a few ways to do it and we’ll show you how.

You can use Google Sheets to track article or essay titles where count is crucial. Or maybe you want to limit the length of data that others enter into your sheet and you need the current counts. Whatever the case, you can use the LEN function in conjunction with options to remove extra spaces from the count or count only specific characters.

Count characters in a cell

The LEN function in Google Sheets works just like it does in Microsoft Excel. Gives you the number of characters in a cell using a simple formula.

RELATED: How to Count Characters in Microsoft Excel

The syntax of the function is LEN(text) where you can use a cell reference or actual text for the argument.

To find the number of characters in cell A1, you would use this formula:

=LEN(A1)

To find the number of characters in a specific text, use the following formula by enclosing the text in quotes:

=LEN("Tomorrow is another day")

The important thing to know about the LEN function is that it counts every character, including numbers, letters, single spaces, non-printing characters, and punctuation.

Count characters in a range of cells

While many Google Sheets functions allow you to use a cell range as an argument, LEN isn’t one of them. However, by adding the SUMPRODUCT function to the LEN formula, you can count characters in a range of cells.

RELATED: How to Calculate a Weighted Average in Excel

The SUMPRODUCT function calculates the sum of arrays or ranges of cells. Its syntax is SUMPRODUCT(array1, array2, ...) where only the first argument is required.

To find the count of cell range A1 to A5, you would use the following formula:

=SUMPRODUCT(LEN(A1:A5))

Count characters without extra spaces

As mentioned, the LEN function counts each character. This means that if you want to count characters in a cell that contains extra spaces, those are also counted.

RELATED: How to remove extra spaces in your Google Sheets data

For example, we have “How-To Geek” in cell A10. Using the LEN function to count the characters, the result is 17 because we have three extra spaces at the beginning and three more trailing spaces at the end.

If you have data in your sheet that contains unwanted spaces, you can remove them with the TRIM function. And by combining LEN with TRIM, you can get the correct character count without extra spaces.

Note that the TRIM function only removes extra spaces, and that the LEN function counts individual spaces such as those between words. So using the following formula, our result is 11.

=LEN(TRIM(A10))

Count instances of specific characters in a cell

One more adjustment you may want to make when counting characters is to count certain characters. You may want to know how many times the letter C appears in the text string of a cell. To do this, you’ll use another Google Sheets function which is SUBSTITUTE.

The SUBSTITUTE function is normally used to replace text in a cell and its syntax is SUBSTITUTE(current_text, find, new_text, occurrence) where the first three arguments are required.

Let’s look at an example and then break down the parts of the formula. Here we will see the number of times the letter C appears in cell A1.

=LEN(A1)-LEN(SUBSTITUTE(A1,"C",""))

The formula breaks down as follows from right to left:

• SUBSTITUTE(A1,"C","") replace each C with whatever is in the quotes, which is nothing.
• LEN(SUBSTITUTE(A1,"C","") counts the number of characters that are not the letter C (substituted).
• LEN(A1) counts the characters in cell A1.

Finally, a minus sign divides the formulas to subtract the second LEN formula from the first, giving us the result that is 3.

One disadvantage of counting specific characters with the SUBSTITUTE function is that it is case sensitive. So if you look at our text and wonder why the result is 3 instead of 4, here’s why.

To remedy this, you can add one more function to the formula and it’s your choice. You can use UPPER or LOWER. The UPPER function converts a letter to uppercase and LOWER converts a letter to lowercase.

So, to count all occurrences of the letter C in our cell, regardless of the case, I would use one of the following formulas:

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"C",""))

=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"c",""))

If the text in your cell contains a lot of uppercase letters, you can use the first formula, but if it contains mostly lowercase letters, you can use the second. The key is to use UPPER with the uppercase letter in quotes and LOWER with the lowercase letter in quotes.

You may not need to count characters in Google Sheets often, but when you do, you’ll have this handy handy. Make sure to bookmark it!

RELATED: How to Replace Text in Google Sheets