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 wordsSample 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.
- LEN function in Google Sheets
- Syntax
- Example 1: Counting the number of characters
- Example 2: Counting the number of letters
- Example 3: Counting the number of occurrences of a letter
- Example 4: Counting the number of letters in multiple cells
- Example 5: Counting the number of words
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)
As you can be seen below, the function counted every character including the spaces before and in between the words.
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.
On pressing Enter, you should get the number of letters with the spaces omitted, as shown below.
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”.
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.
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),” “,””)) + 1The 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.
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