Though word and character count in Google Sheets are used in rare cases, it is still the functionality that some of us expect to see right in the menu. But unlike Google Docs, for Google Sheets, it is the LEN function that does that.
Even though there are many different ways to count characters in spreadsheets, today's blog post will cover the LEN function as its main purpose in tables is to – well, count :) However, it is hardly ever used on its own. Below you will learn how to use Google Sheets LEN correctly and find the most wanted formulas to compute characters in spreadsheets.
The main and the only purpose of the LEN function in Google Sheets is getting the string length. It is so simple that it even requires only 1 argument:
=LEN("Yggdrasil")
=LEN(A2)
Let's see if there are any peculiarities in using the function in spreadsheets.
I'll start with the simplest operation: do a character count in Google Sheets the most common way – by referencing a cell with the text using the LEN function.
I enter the formula to B2 and copy it down the entire column to count characters in each row:
=LEN(A2)
You may think that in a similar manner you could do a character count for the entire range of cells, like this: LEN(A2:A6)
. But, as bizarre as it is, it doesn't simply work this way.
To total characters in several cells, you should wrap your LEN in SUMPRODUCT – the function that tallies up the numbers from entered ranges. In my case, the range is returned by the LEN function:
=SUMPRODUCT(LEN(A2:A6))
Of course, you could incorporate the SUM function instead. But SUM in Google Sheets doesn't process arrays from other functions. To make it work, you will have to add another function – ArrayFormula:
=ArrayFormula(SUM(LEN(A2:A6)))
As I noted above, Google Sheets LEN function counts each and every character it sees including spaces.
But what if there are extra spaces added by mistake and you don't want to consider them for the result?
For cases like this, there's the TRIM function in Google Sheets. It checks the text for leading, trailing, and repeated spaces in-between. When TRIM is paired with LEN, the latter doesn't count all those odd spaces.
Here's an example. I added spaces in different positions in column A. As you can see, when on its own, Google Sheets LEN counts them all:
=LEN(A2)
But as soon as you integrate TRIM, all extra spaces are ignored:
=LEN(TRIM(A2))
You can go further and make your formula disregard even those single spaces between words. The SUBSTITUTE function will assist. Though its main purpose is to replace one character with another, there's a trick to make it reduce spaces completely:
Now try and assemble all these into Google Sheets LEN and you'll see that no space is taken into account:
=LEN(SUBSTITUTE(A2, " ", ""))
The same tandem of Google Sheets LEN and SUBSTITUTE is used whenever you need to count specific characters, letters, or numbers.
In my examples, I'm going to find out the number of occurrences for the letter 's'. And this time, I'll start with a ready-made formula:
=LEN(A2)-LEN(SUBSTITUTE(A2, "s", ""))
Let's break it down to pieces to understand how it works:
The result difference shows how many 's' there are in the cell:
The thing is, the SUBSTITUTE function is case-sensitive. I asked it to take all instances of 's' in lowercase and so it did.
To make it ignore text case and process letters in both lower and upper cases, you will have to call one more Google Sheets function for help: LOWER.
It is as simple as Google Sheets LEN and TRIM because all it requires is the text:
=LOWER(text)
And all it does is turn the entire text string into lower case. This trick is exactly what you need to make Google Sheets count specific characters no matter their text case:
=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2), "s", ""))
=SUMPRODUCT(LEN(A2:A7)-LEN(SUBSTITUTE(LOWER(A2:A7), "s", "")))
When there are multiple words in cells, chances are you will need to have their number instead of the Google Sheets string length.
And though there are multiple ways of doing so, today I will mention how Google Sheets LEN does the job.
Remember the formula I used to count specific characters in Google Sheets? In fact, it will come in handy here as well. Because I'm not going to literally count words. Instead, I will count the number of spaces between the words and then simply add 1. Have a look:
=LEN(A2)-LEN(SUBSTITUTE((A2), " ", ""))+1
Finally, I'd like to share a Google Sheets formula that you can use to count specific words.
Here I have The Mock Turtle's Song from Alice's Adventures in Wonderland:
I want to know how many times the word 'will' appears in each row. I believe you won't be surprised if I tell you that the formula I need consists of the same functions as before: Google Sheets LEN, SUBSTITUTE, and LOWER:
=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2), "will", "")))/LEN("will")
The formula may look scary but I can assure you that it's easy to comprehend, so bear with me :)
Thus, if 'will' appears once, the number is 4 as there are 4 letters in the word. If it appears twice, the number is 8, and so on.
=SUMPRODUCT((LEN(A2:A7)-LEN(SUBSTITUTE(LOWER(A2:A7), "will", "")))/LEN("will"))
As you can see, all these cases of character-count are solved by the same patterns of the same functions for Google Sheets: LEN, SUBSTITUTE, LOWER, and SUMPRODUCT.
If some formulas still confuse you, or if you're not sure how to apply everything to your particular task, don't be shy and ask away in the comments section below!
7 responses to "Count words and characters in Google Sheets: LEN function"
Thanks for this great post!! Very clear and informative.
I have 1 question though...
=LEN(A2)-LEN(SUBSTITUTE(A2, "s", ""))
This formula is almost exactly what I need. Except, I dont want to count for "s", I want to count for the value in a specific cel (say C1, which will contain S). This is so I can easily change the S I am looking for into a different letter.
But I cannot seem to adjust the formula to make it count the text in a cell...
Curious to see if you have a solution.
Thanks in advance!
Sorry... solved it.... (forgot some brackets :S)
=LEN(A2)-LEN(SUBSTITUTE((A2);(C1);""))
Thank you for your feedback.
You don't actually need brackets for cell references. Instead, I'd recommend using dollar signs to make the reference absolute so it doesn't change when copied to other cells:
=LEN(A2)-LEN(SUBSTITUTE(A2;$C$1;""))
Hello! This was an excellent post with formulas I hope to try. Here’s what I’m hoping it could be used for-can you assist me in knowing if it will work? I have to sift through a lot of online reviews/comments and provide analysis on “how many times a reviewer used certain words-like “happy”, “angry”, compassionate, etc. my hope is to use one of these words in the string of cells to easily identify the count. I’m doing something else which is quite tedious.. many thanks in advance for your kind assistance!
Hello Haley,
If I understand you correctly and you need to count the number of times a certain word appears, you should use the COUNTIF function. We have an entire blog post about it, please take a look:
Google Spreadsheet COUNTIF function with formula examples
So I run a high ranking guild in a video game and keep track of everyone's attacks, draws, and losses when we fight other guilds. We have so many attacks and I keep track of everything in the format 'w,d,L' etc. and I have the track record spread over multiple rows. For example, I am using this string '=SUMPRODUCT(LEN(J186:W186)-LEN(SUBSTITUTE(J186:W186, "w", "")))' to calculate totals over a row but need to add the total between multiple rows. How do I add 2 different rows together in this formula? I apologize if I'm not as clear as I'd like to be about this.
Essentially, I want to add that row with:
=SUMPRODUCT(LEN(B222:F222)-LEN(SUBSTITUTE(B222:F222, "w", "")))
Hello!
If I understand your task correctly, the following formula should work for you:
=SUM(LEN(B222:F222))-SUM(LEN(SUBSTITUTE(B222:F222, "w","",1)))
I hope my advice will help you solve your task.