Unlike Google Docs, Google Sheets doesn’t automatically keep track of character counts for you. If you need to count all or specific characters in a cell, there are a few ways to accomplish this, and we’ll show you how.
You might use Google Sheets for tracking titles of articles or essays where the count is crucial. Or maybe you want to limit the length of data others enter in your sheet and need the current counts. Whatever the case, you can use the LEN function along 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. It gives you the number of characters in a cell using a simple formula.
The syntax for 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 specific text, use the following formula placing the text within quotes:
=LEN("Tomorrow is another day")
The important thing to know about the LEN function is that it counts each character including numbers, letters, single spaces, nonprinting characters, and punctuation.
Count Characters in a Range of Cells
While many Google Sheets functions allow you to use a cell range as the argument, LEN isn’t one of them. However, by adding the SUMPRODUCT function to the LEN formula, you can count characters in a cell range.
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 the cell range A1 through A5, you would use the following formula:
=SUMPRODUCT(LEN(A1:A5))
Count Characters Without Extra Spaces
As mentioned, the LEN function counts every character. This means that if you want to count characters in a cell that contains extra spaces, those get counted too.
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 another three 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.
Keep in mind that the TRIM function only removes extra spaces, and that the LEN function does count single spaces like those between words. So using the below 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. Maybe you want to know how many times the letter C appears in a cell’s text string. To do this you’ll employ 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 pieces of the formula. Here, we’ll 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","")substitutes every C with what’s in the quotes which is nothing.LEN(SUBSTITUTE(A1,"C","")counts the number of characters that are not the (substituted) letter C.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 which is 3.
One downfall to counting specific characters with the SUBSTITUTE function is that it’s case-sensitive. So if you examine our text and are wondering why the result is 3 instead of 4, this is the reason.
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 case, you 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 many uppercase letters, you might use the first formula, but if it contains mostly lowercase letters, you can use the second one. 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 how-to. Be sure to bookmark it!
