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.