How to count characters in a cell in Google Sheets

Unlike Google Docs, Google Sheets does not automatically take the number of characters into account. If you need to count all characters in a cell or some of them, there are several ways to achieve this and we will show you how.

You can use Google Sheets to track titles of articles or essays where character count is critical. Or maybe you want to limit the length of data other people enter into your sheet and you need current counts. Either way, you can use the LEN function with options to remove extra spaces from the count or count only certain characters.

HOW TO TRACK STOCKS WITH GOOGLE SHEETS

Count characters in a cell

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

The function syntax is LEN(text), where you can use a cell reference or actual text as an argument.

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

=LEN(A1)

LEN for a cell reference

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

=LEN(“Tomorrow is another day”)

LEN for text

What you need to know about the LEN function is that it counts every character, including numbers, letters, single spaces, non-printed characters, and punctuation.

Count characters in a range of cells

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

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 number of cells in the range A1 to A5, you will use the following formula:

=SUMPRODUCT(LEN(A1:A5))

SUBPRODUCT function with LEN

Count characters without extra space

As we 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.

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 at the end.

LEN to count extra spaces

If your sheet data contains unwanted spaces, you can remove them using the TRIM function. And by combining LEN and TRIM, you can get the correct number of characters without extra spaces.

Remember that the TRIM function only removes extra spaces, and the LEN function counts single spaces, such as those that separate words. So, using the formula below, our result is 11.

=LEN(TRIM(A10))

LEN with TRIM remove extra spaces

Count occurrences of specific characters in a cell

Another 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 a cell’s text string. To do this, you need to use another function of Google Sheets, namely the SUBSTITUTE function.

The SUBSTITUTE function is normally used to replace text in a cell. Its syntax is as follows: SUBSTITUTE(current_text, find, new_text, occurrence), the first three arguments being mandatory.

Let’s look at an example and then break down the elements 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","") remplace chaque C par ce qui se trouve entre guillemets, c'est-à-dire rien.
LEN(SUBSTITUTE(A1, "C","") compte le nombre de caractères qui ne sont pas la lettre C (substituée).
LEN(A1) compte les caractères de la cellule A1.

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

LEN with SUBSTITUTE to count specific characters

One of the disadvantages 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, that’s the reason.

To remedy this, you can add an additional function to the formula and the choice is yours. 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 your cell text 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.

LEN using UPPER and LOWER

You might not often need to count characters in Google Sheets, but when you do, you’ll have this handy how-to. Don’t forget to add it to your favourites!

Leave a Comment