In this article you will learn about what Google Sheets is, what it offers to its users, how you can use a specific function that comes with Google Sheets to count the number of characters a section of your data, and alternate uses for this function that allow you to achieve different objectives.
What is Google Sheets?
Google Sheets is a web-based spreadsheet program developed by Google. It allows you store and organize your data – like numbers, dates, names – into an ordered structure based on rows and columns.
If you are working with other people, every change you make on a Google Sheet is automatically and instantly displayed to your teammates. To share a Google Sheet with others you can simply give them the link to the Sheet and they will have access to it (you can choose whether to give them read-only access or if you want them to be able to alter the content of the file). This makes Google Sheets an amazing application for collaborative work.
Google Sheets also provides a wide array of operations and functions to perform with the data stored. One of these functions is called the LEN function. This function will be the focus of our article since it’s the one that allows us to count characters in Google Sheets.
The LEN function
The LEN function allows us to get the length of a string (text). It is a function that only requires one argument.
Considering that our purpose is counting characters in Google Sheets, we can rapidly tell that the LEN function will be vital in our endeavor.
Let’s look at how this function works.
LEN function and its description in Google Sheets
This function has the following structure:
=LEN(text)
“text” is the string whose characters we need to count. This could be a different cell, or an explicit text. For example:
=LEN(“hello”)
The result of this function will be the number 5.
=LEN(A2)
The result of this function will depend on what is inside the A2 cell in our sheet.
If our sheet has the word Welcome inside our A2 cell. Then LEN(A2) will be interpreted as LEN(“Welcome”) and the result of the function will be the number 7.
An important factor to keep in mind when using the LEN function is that, when counting, it will consider every single character. This means it will count every letter, number, space, punctuation mark, etc.
Later in this article we will see how we can avoid counting the spaces in our text.
How to count characters using the LEN function.
We’ve already seen that the LEN function counts the characters inside a text. Now we will review how we can use this to count the characters inside a single cell, and then we will see that this same function can be used to count the characters inside multiple cells.
Counting characters in a single cell
As we have already seen when looking at how the LEN function works, we can name the cell whose characters we want to count inside the () and the LEN function will provide us with the correct number.
For example:
If this is our sheet. Then =LEN(B3) will result in the number 5, since the word Count has five characters.
Counting characters in multiple cells
To be able to count the total of characters inside multiple cells, we will need the aid of another function called SUMPRODUCT().
What we will do is define the range of the cells whose characters we need to count, place that range inside our LEN function, and finally wrap SUMPRODUCT around our LEN function.
Here’s what that looks like:
=SUMPRODUCT(LEN(FirstCell:LastCell))
Let’s look at an example:
If our Google Sheet looks like this:
Then the result of =SUMPRODUCT(LEN(C2:C5)) will be the number 41 (remember: spaces also count as characters).
Counting characters without spaces
We already know how to count the characters in a single cell and how to count the characters in a range of cells. But there’s a possible problem: if you, by mistake, have multiple spaces or extra spaces, these will be counted as additional characters.
How can we make sure we are not counting these repeated or extra spaces? By using the TRIM function.
The TRIM function is a function that will take a text and remove any extra or repeated spaces within it.
If we wrap our LEN function around the TRIM function, we will be able to get a count of all the characters in our range without also counting those extra or repeated spaces.
Here’s what that looks like:
=LEN(TRIM(Cell))
And if we want to do this same process but for multiple cells instead of one, then we only need to place this inside a SUMPRODUCT.
Here’s what that looks like:
=SUMPRODUCT(LEN(TRIM(FirstCell:LastCell)))
Let’s look at an example:
If our Sheet looks like this:
Then the result of = SUMPRODUCT(LEN(TRIM(A1:A5))) will be the number 31.
Conclusion
We hope this answered any questions you had about counting characters in Google Sheets. If you need any additional features or have trouble counting the characters in Google Sheets, feel free to copy and paste your text into the character counter on our home page.