How To Count Characters In Google Sheets? (100% Tested Methods)

At times you may need to know how many letters and numbers are there in a cell or a specified cell range, isn’t it? But we all know that the in-built counter is missing in GS. So, how to count characters in Google Sheets? Is there a way?

You can count characters in a cell by using the LEN function. And you need to use “=LEN(<reference cell>)” as the main syntax. You can also use the “=SUMPRODUCT(LEN(<cell range>))” formula to calculate the number of characters in a specified cell range.

However, both these formulas will count all the spaces and symbols with them. But worry not, as there are ways to count just the letters and numbers. So, without any further ado, let’s see how we can count the easy way in Google Sheets.

How To Count Characters In Google Sheets?

While other Google Workspace apps like Google Docs have an inbuilt character and word counter, Google Sheets doesn’t have any such thing. But it is still possible to count characters in Google Sheets, as there are several methods available.

So, here I’m going to give you all the working methods. I’m going from the easiest to the hardest!

Using The LEN Function For Character Count

The LEN function is the most useful one in Google Sheets to count characters. Although it is a simple function that counts the length of the string, you can use it on complex formulas (which I’ll also demonstrate here).

It also goes well with the IF formula because you can set conditional reasoning along with the length function.

Explanation Of The LEN Function In Google Sheets

I’ve already told you that LEN is the handiest function for character count in Google Sheets. It is a simple formula that counts the length of a string (in number) in any specific cell or cell range.

The main syntax is “=LEN(<Text/Cell Reference>)” in GS.

You can use the text directly in the formula, like “=LEN(“Mighty Spreadsheet”),” or you can directly refer to a cell, like “=LEN(A1),” to count the characters.

Note: The LEN formula will count everything in the specified string, including empty spaces, punctuations, symbols, and numbers. So, it is not possible to get only the alphabet count by using just this formula.

Step-By-Step Instructions For Using The LEN Function With Examples

Once you understand the basics of the LEN function, you are now ready to know how to count characters in Google Sheets. Let’s say we have “Mighty Spreadsheet” written on the A1 cell, and we want to count the character and have the output in the B1 cell.

We will now use both the direct text and the reference cell methods. Here are the steps!

• Step 1: Launch Google Sheets and open your spreadsheet.
• Step 2: Click on the output cell. (Here it is B1)
• Step 3: Click on the Formula (fx) bar.

• Step 4: Enter the “=LEN(“Mighty Spreadsheet”)” formula and hit the enter button.
• Step 5: It will return “18” in the B1 cell as the total character count is 18, including a space.
• Step 6: To reference the cell directly, alter the with the “=LEN(A1)” one.

• Step 7: It will also return “18” in the B1 cell.

If you want to count the characters for a whole column, you can insert this formula as well. And to do that the right way, follow my latest guide on applying a formula to an entire column in Google Sheets.

Counting Only Certain Characters 

Not only the entire string, but you can also now even count the number of times a specific character appears inside a string in Google Sheets, such as how many times the letter “a” appears in a sentence.

And we will be using the LEN function along with the SUBSTITUTE function to count it. The main syntax is “=LEN(<reference cell>)- LEN(SUBSTITUTE(<reference cell>,”<character to count>”,””))” here.

Let’s say we have “Mighty Spreadsheet” in the A1 cell, which we will use as the reference cell. And we want to count the number of times “e” appears in that cell. You will now try to get the output in the B1 cell.

• Step 1: Click on the output cell (here it is B1).
• Step 2: Click on the Function (fx) field.

• Step 3: Enter the “=LEN(A1) - LEN(SUBSTITUTE(A1, "e", ""))” formula.
• Step 4: Hit the enter button, and it will return “3” as the output in the B1 cell.

As you can see that the formula returns 3 because the letter “e” appears three times in the “Mighty Spreadsheet” phrase. However, this formula is case-sensitive, and it will only count “e” in small cases.

If you want to count a specific character regardless of its case, just replace the previous formula with the “=LEN(A1)- LEN(SUBSTITUTE(UPPER(A1), “e”,””)) or =LEN(A1)- LEN(SUBSTITUTE(LOWER(A1), “e”,””))” formula.

Note: It will be better to count the characters of a row or column after you group them accordingly. And to do that, follow my detailed guide on grouping and collapsing rows and columns in Google Sheets.

Counting With And Without Empty Spaces

LEN is surely the most useful function for counting characters in Google Sheets. But I’ve also mentioned earlier that it has a limitation. This function counts all the spaces in a string.

But at times, there can be blank spaces at the beginning or end of any word or sentence. Besides, there is always a chance of double space due to typing errors. So, how to sort it out?

We will be using the LEN function along with the TRIM function to remove all the extra spaces from a string. And the main syntax will be “=LEN(TRIM(>reference cell>))” here.

Let’s say we have the “Mighty Spreadsheet teaches us all the Google Sheets methods” sentence in the A1 cell. But it has some extra spaces inside it, which needs to be removed before counting. Here are the steps.

• Step 1: Click on the output cell (here it is B1).
• Step 2: Click on the function (fx) field.

• Step 3: Enter the “=LEN(TRIM(A1))” formula.
• Step 4: Hit the enter button, and it will remove all the extra spaces and return “59” as the output.

You can see that it has removed all the extra spaces from the beginning, ending, or at the middle of the words before counting the characters. But yes, it will keep a single space after every word other than the last one.

However, it is also possible to remove all the spaces from a string before counting it. And to do that, we will be using the LEN function along with the SUBSTITUTE function.

The main syntax will be “=LEN(SUBSTITUTE(<reference cell, “<blank space>”,””))” here. The steps are easy!

• Step 1: Click on the output field (B1).
• Step 2: Click on the function field and enter the “=LEN(SUBSTITUTE(A1,“ ”,””))” formula.
• Step 3: Hit the enter button, and it will return “51” as the output.

As you can see, we got “59” as the output while using the previous formula. And with this one, we got “51” as the output because this formula has deleted all the eight spaces between words.

Google Sheets Count Characters In Specific Cell

It is possible to count characters in a cell in Google Sheets by using the same LEN function. You just need to use the cell as the reference cell inside the formula to count the characters. And the syntax will be “=LEN(<reference cell>).”

So, if you want to count the characters of the A1 cell, simply use the “=LEN(A1)” formula. But remember that it will count all the spaces, special characters, symbols, and even punctuations.

If you want to count just the letters or the number, use the “LEN(TRIM(A1)” formula instead the previous one.

Bonus: Do you know that you can now even insert emojis in GS? To know more, follow my comprehensive guide on inserting emojis and special characters in Google Sheets.

Google Sheets Count Characters In Specific Range

Besides single cells, you can now also count the characters of an entire range. And it can be done by using the same character count formula in Google Sheets. We will primarily be using the LEN function. But we will also use the SUMPRODUCT function to get the total count for a specific range.

The main syntax will be “=SUMPRODUCT(LEN(<cell range>))” here. Now, let’s say we want to count the total characters of A1 to A10 cells. Here are the steps to do it!

• Step 1: Launch Google Sheets and open your spreadsheet.
• Step 2: Click on the output cell (where you want the total character count to be displayed).
• Step 3: Now, click on the function (fx) field.

• Step 4: Enter the “=SUMPRODUCT(LEN(A1:A10))” formula.
• Step 5: Hit the enter button, and it will display the total character count in the output cell.

The limitation of the LEN function will also carry forward here. So, you’ll get the total character count, including the spaces and symbols. To remove those, combine the TRIM function in this formula.

How To Count Words In Google Sheets?

I’ve already demonstrated how to count letters in Google Sheets. But at times, you may need to count the words as well. While Workspace apps like Google Docs have an inbuilt word counter, Google Sheets doesn’t have any such thing.

However, we can use some formulas to do it. The main functions we will be using here are LEN along with TRIM and SUBSTITUTE. The main syntax is “=LEN(TRIM(<reference cell))-LEN(SUBSTITUTE(<reference cell>,” “,””))+1” here.

Let’s say we have the “Mighty Spreadsheet teaches us all the Google Sheets methods” sentence in the A1 cell. And we want to count how many words there are in this sentence. Here are the steps.

• Step 1: Click on the output cell (here it is B1).
• Step 2: Click on the Formula (fx) field.

• Step 3: Enter the “=LEN(TRIM(A1)) - LEN(SUBSTITUTE(A1, " ", "")) + 1” formula.
• Step 4: Hit the enter button, and it will return “9” as the output in the B1 cell.

As you can see that this formula returns 9 in the B1 cell because the sentence has 9 words in total. However, if you apply this to an entire column, the cells that have blank values will return 1 as the output, even when there are no words in the blank space.

This issue can be sorted using the IF function. And the formula will be “=IF(A1=””, 0, LEN(TRIM(<reference cell>))-LEN(SUBSTITUTE(<reference cell>,” “,””))+1)” here.

Note: It is now possible to adjust the row heights in Google Sheets, which were previously missing. Follow my comprehensive guide on adjusting row height in Google Sheets to know more.

Automating Character Count in Google Sheets

If you are not good with the formulas, especially with the complex ones, you can even automate the character counting. And there are two ways to do that.

The first one is by using Apps Script. And the second one is by using already existing add-ons. I’m now going to give you the details of both these methods.

Using Scripts To Count Characters

Google’s own cloud-based JavaScript platform, Apps Script, is a great way to automate tasks in Workspace apps, such as Google Sheets and Google Docs.

While using Apps Script, you can write custom codes to count the characters in Google Sheets. Here is the step-by-step guide you need to follow!

• Step 1: Launch Google Sheets and open your spreadsheet.
• Step 2: Click on the “Extension” option and then on the “Apps Script” option.

• Step 3: Delete the existing lines in the editor and paste the following code.

function countCharacters() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sheet.getActiveCell();
var cellValue = cell.getValue();
var characterCount = cellValue.length;
Logger.log("Character count: " + characterCount);
}

• Step 4: Click on the “Save Project” (Floppy icon) option.
• Step 5: Name the script “Count Characters” and exit from the Apps Script.
• Step 6: Relaunch Google Sheets and select the reference cell.
• Step 7: Click on the “Extension” option and then on the “Apps Script” option.
• Step 8: Select “Count Characters” from the library.

• Step 9: Click on the “Review permission” option once you get a popup and allow the necessary permissions.
• Step 10: You can now check the character count of that reference cell in the “Logs” panel.

Note: It is now possible to refer to a different spreadsheet in your working sheet. And to do that, check out my newest guide on how to reference another spreadsheet in Google Sheets.

Utilizing Add-Ons For Character Counting

If you are not good with Apps Script, you can still rely on existing add-ons on Google Marketplace. The Power Tools by Ablebits is one of the best extensions that can count characters. Here are the steps.

• Step 1: Click on the “Extension” option from the header menu.
• Step 2: Hover your mouse over the “Add-ons” option and click on the “Get add-ons” option.
• Step 3: Click on the search bar in the newly launched window.

• Step 4: Type “Power Tools” in the designated search field and hit the enter button.
• Step 5: Click on the first result and then on the “Install” button.
• Step 6: Allow necessary permissions to complete the installation process.
• Step 7: Relaunch Google Sheets and click on the “Extension” again.
• Step 8: Now, click on the “Power Tools” option, and a side widget will load.
• Step 9: Select the reference cell and navigate to the Power Tools side widget.
• Step 10: Click on the “Character Count” tab and select your preferences.
• Step 11: Finally, click on the “Count Characters” button to see the result.

Note: This feature is only available in the premium version of Power Tools, for which you need to take a subscription. Right now, it is not available in the basic (free) version.

Conclusion

The LEN function is sufficient for the basic character count in Google Sheets. And by using it along with the TRIM function, you can even cancel all the extra spaces before going for the actual count.

But, if you need more control over the count and if you are not good with the formulas, it is better to use the Power Tools add-ons available on the Marketplace. You can also use our custom formula for Apps Script to get it done!

FAQs

Q. What is the limit character count in Google Sheets?

No, it is not possible to limit the character count in Google Sheets. However, you can definitely limit the max character in a cell. And to do that, you need to use the “=REGEXMATCH(<reference cell>&””,”^(.){1,<max character limit>}$”)” formula in the conditional formatting.

Q. Is it possible to count characters in multiple cells simultaneously?

Yes, it is possible to count characters in multiple cells and for a specified cell range in Google Sheets. You can easily do it by using the “=SUMPRODUCT(LEN(<cell range>))” formula. Just specify your range in the “<cell range>” space to get the count.

Q. Can the COUNT function be used to count characters other than letters or numbers?

No, it is not possible to use the COUNT function to count the characters in a specified cell in Google Sheets. You can do it in Excel by using the COUNTCHAR function. But for Google Sheets, you have to use the LEN function.

Q. Can I count characters in a specific range or exclude certain cells from the count?

Yes, you can surely count characters in a specified range by using the LEN and the SUMPRODUCT function. However, it will not be possible to exclude certain cells from that selected cell range.