How to Use the Len Function in Google Sheets Count the Number of Letters

How to use the LEN function in Google Sheets to count the number of letters
Reading Time: 6 minutes

What it does – To count the number of letters using the LEN function for different cases

Syntax:
=LEN(text) //Generic formula to count the number of characters

Sample Usage:
=LEN(SUBSTITUTE(A6, " ", "")) //Count number of letters in cell A6

=LEN(A2)-LEN(SUBSTITUTE((A2),”a”, ””)) //Count occurrences of a particular letter–here the letter a

=SUM(LEN (SUBSTITUTE(A5, " ", "")), LEN (SUBSTITUTE(A6, " ", ""))) //Count letters in multiple cells

=LEN(TRIM(A2))-LEN(SUBSTITUTE((A2),” “,””)) + 1 //Count number of words

Sample Google Sheets template with formula here.

We often want to quickly determine the length of words in a given sentence or paragraph. Other times we might be working in spreadsheets and want to get word counts. While there are several ways to do this, Google Sheets offers a simple and quite convenient one.

What is the LEN function in Google Sheets?

The LEN function in Google Sheets returns the length of a string. It is a basic function that counts the total number of characters passed into it as parameters. 

Note: The function also considers space as a character and will include it. To avoid unexpected values, you can ensure the text parameter doesn’t have additional spaces.

In this tutorial, we will look at how to use the LEN function to count the number of letters in Google Sheets and other creative ways we can apply it by combining it with other functions to achieve more.

How to use the LEN function to count the number of letters in Google Sheets

We are going to use a series of examples to explore the LEN function in Google Sheets. We will gradually progress from basic applications to more complex ones.

Example 1: Use the LEN function in Google Sheets to count the number of characters

We have included some text in a few cells which we are going to count with the LEN function. 

Enter the LEN function in a cell and pass in A5 

= LEN (A5)
Using the LEN function in Google Sheets

As you can be seen below, the function counted every character including the spaces before and in between the words. 

Using the LEN function to count the number of characters
Counting the number of characters using the LEN function in Google Sheets

While this is good for things like writing meta-descriptions and other forms of writing that require a specific number of characters, it isn’t so useful for those who want to count letters. So let us look at how we can modify the LEN function to count letters only.

Example 2: Counting letters with the LEN function in Google Sheets

To count letters we will be combining the LEN function with another Google Sheets function–the SUBSTITUTE function.

The substitute function has a simple syntax. It takes in three mandatory parameters.

Below is the syntax of the SUBSTITUTE function in Google Sheets:

 =SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
  • the text_to_search is the text you want to substitute values with
  • search_for is the part of text_to_search you want to substitute another value for
  • replace_with is the value you want to substitute into the text_to_search

Now that we’ve got familiar with the SUBSTITUTE function, let’s see how we can use it with the LEN function

Enter the formula below into a cell

= LEN (SUBSTITUTE(A6, " ", ""))

This is basically the same text we used in the first example. But this time we are trying to do things differently:

We are substituting “” for ” ” (non-space for space) which is another way of saying we are trying to replace all the space characters with no space.

LEN function with SUBSTITUTE function to count the number of letters

On pressing Enter, you should get the number of letters with the spaces omitted, as shown below.

Using the LEN function to count the number of letters
LEN function in Google Sheets counting the number of letters

What the SUBSTITUTE function did was eliminate all the space. So, instead of the 30 that we got in the first example for A4, we got 26 which is the actual number of letters in the text.

Example 3: Using the LEN function to count the number of times a particular letter appears

This is probably one of the most ingenious ways to use the LEN function.

We might decide that we want to know how many times a letter, say “a”, appears in a sentence or a paragraph. The LEN function once again is there to the rescue. 

We are still going to use the same set of formulas but in a slightly different way.

Below is the function to count specific letters: 

=LEN(A2)-LEN(SUBSTITUTE((A2),”a”, ””))

What this basically means is count the number of characters in cell A2 and subtract the total characters without “a”, which gives the total number of “a”.

Counting the occurrence of a particular character
Counting the occurrence of a particular character

The LEN function in Google Sheets returns the number of times “a” appears in the sentence which is 3, 4, and 8 respectively for the 3 cells.

Example 4: Using the LEN function in Google Sheets to count letters in multiple cells

So far we have dealt with the text contained in a single cell, but what if we want to count the total letters in multiple cells? 

Well, we can easily do this by combining the LEN function in Google Sheets with yet another function, the SUM function.

Let’s quickly consider its syntax of the SUM function.

= SUM (value 1, [value 2,..]
  • value 1 can be a number or a range of values to be added together.
  • value 2 is the second number or range of numbers to add to value 1.

Therefore, to count all the letters (non-space characters) in several cells we will be combining 3 functions: LEN function, SUBSTITUTE function, and SUM function. 

Enter the formula below into a cell

= SUM ( LEN (SUBSTITUTE(A5, " ", "")), LEN (SUBSTITUTE(A6, " ", "")))

This is simply us using the SUM function to add up all the letters returned by the LEN function in cells A5 and A6.

Numbers of characters in multiple cells
Counting the number of letters in multiple cells with LEN function

The total number of letters in the two cells is 59. We can add as many cells as we want to the formula but each would have to go in through the SUBSTITUTE function first to remove the spaces in them.

Example 5: Using the LEN function in Google Sheets to count words

Characters and letters are both interesting things to keep a tab on but you would agree that most times what we want to find out is the number of words. 

Unfortunately, the LEN function can only count the length of texts, not the number of words. 

But there’s a way around this limitation. It just requires a little observation:

  • Words in a sentence are usually always separated by spaces.
  • If we can determine the number of times spaces appeared in a sentence i.e between the first and last words of that sentence
  • Then all we need to do is add 1 to that number. 

With this knowledge in mind, it’s quite easy to combine a group of formulas to automate the whole process for us. We can combine 3 formulas to achieve this

=LEN(TRIM(A2))-LEN(SUBSTITUTE((A2),” “,””)) + 1

The introduction here is the TRIM function. This is a basic function used for removing the spaces before and at the end of sentences.

So, here is a breakdown of the formula above:

  • The TRIM function removes spaces at the beginning and end of the sentence in the cell. Then pass that into the LEN function. This represents the total number of characters with space between words.
  • The SUBSTITUTE function removes all spaces in between words and passes that into LEN. This represents the total number of characters with no space
  • Total number of characters with internal space – Total number of characters with no space = Total spaces between words in the sentence.
  • Add 1 to this to represent space before the first word = total number of words in the sentence.
Counting the number of words
Counting words with the LEN function in Google Sheets

The number of words in our sentence is 5. That’s how you make a word count with the LEN function in Google Sheets. 

Conclusion

The LEN function is very versatile as we have seen in this tutorial. You can combine it with other functions in so many ways to achieve the needed result.

See also

You can find more articles on interesting Google Sheets functions and general Sheets usage on our blog, try some of these:

CHAR function and how to use it in Google Sheets

Separate the first and last names in Google Sheets

Use LEFT and RIGHT functions in Google Sheets