How to count characters in Google Sheets

Unlike Google Docs, Google Sheets doesn’t have an in-built word count option. To count characters in Google Sheets, you have to improvise.

The LEN function is used for counting characters in spreadsheets. This function is more powerful than the in-built word count function in Google Docs. You can specify what you want to count and also specify the conditions.

4 ways to count characters in Google Sheets

  • Count all the characters in a cell
  • Count all the characters in a column
  • Count the characters and ignore spaces
  • Count specific characters

Method 1: Count all the characters in a cell

This method is useful when cells contain only one word. To use it, type the LEN function. Next, make a reference to the cell you want to count. Let’s assume you want to count cell A2. The syntax will be:

=LEN(A2)

You may want to know the total number of characters within a column. To count the number of characters in a column, pay attention to the next method.


Method 2: Count all the characters in a column

The LEN function has a certain drawback. You cannot use it over a range of cells. For example, if you type LEN(A2:A6), it wouldn’t work. To sum up the characters in a range, you need the SUMPRODUCT function. 


Method 3: Count the characters and ignore spaces

Step 1: TRIM function

Another drawback of the LEN function is that it counts all the characters in a cell. These characters may include spaces and punctuation marks. Some cells may even include double spaces by mistake. 

If you want to ignore the odd space in words, you will need the TRIM function. This function ignores all the odd spaces. The syntax will be as follows:

=LEN(TRIM(A2))

However, the TRIM function allows single spaces. As a result, the LEN function counts them. So if you want to exclude all spaces as you count characters, pay attention to the next step.

Step 2: SUBSTITUTE function

Here, the substitute function replaces all spaces with literally nothing. The LEN function will ignore the spaces since they are now replaced with nothing. Here is the syntax:

=LEN(SUBSTITUTE(A2, " ", ""))

Method 4: Count specific characters

Step 1: SUBSTITUTE function

When trying to count specific characters, you need the SUBSTITUTE function. You don’t have to memorize the syntax. Once you understand how it works, you’ll remember it easily.

The function works in 4 steps:

  • It counts all the characters in the cell. 
  • It removes the characters you want to count from the cell.
  • It counts the rest of the characters in the cell. 
  • It subtracts the new sum from the previous sum.

Let’s assume you want to count the number of times ‘s’ appears in cell A2.

The syntax will be as follows:

=LEN(A2)-LEN(SUBSTITUTE(A2, "s", ""))

However, the SUBSTITUTE function is case-sensitive. The function will not work if the dataset contains both upper and lower cases. Pay attention to the next step.

Step 2: Lower case function

To count both lower and upper case characters, you need to use the LOWER function. Here is how it works. It converts every character in the cell to  lowercase before running the rest of the formula. Let’s assume you want to count the characters in cell A2. The syntax will be as follows:

=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2), "s", ""))

Wrapping up

We use the LEN function to count in Google Sheets. This function is powerful, and you can specify exactly what you need it to do. The methods above will help you count characters, but you can also use the function to count words. 

Leave a Comment