In this article:

May 8, 2024

In this article we show you multiple methods to get the character count in Google Sheets for your data entry or inventory needs.

LEN is the Google Sheets function for length of text and will return the number of characters inside your specific cell or string. Follow these simple steps below:

When using a specified string inside text, always remember to enclose it with quotation marks.

Note: When using this formula, it tallies each individual part of your string, including all spaces, numbers, and even punctuations. This will also only work for a single cell. If you wish to apply this to an entire range, either drag your formula down to the adjacent cells of your other text and manually sum these values up or follow the instructions below.

This will treat your range as an array inside the SUMPRODUCT function and will add the character tally for each cell inside the array. You can use this for multiple arrays too! Just always make sure that you are using a valid range for your SUMPRODUCT array function to work properly.

To quantify a singular specific character from a string, follow the steps below.

Replace "cell number" with the the cell you want to count. In our case we are using cell a2. Replace "character to count" with the character you want to count. In our example we want to count instances of the character "a"

**=LEN(A2)- LEN(SUBSTITUTE(A2,"a",""))**

Google Sheets follows a reverse approach to tally this and a breakdown of this procedure is seen below:

- LEN(A2) adds up the total in our original text in cell A2.
- SUBSTITUTE((A2,”a”,””)) removes our specified character (in this case lets use “a”) from the text string
- LEN(SUBSTITUTE(A2,"a","")) tallies the number of all other characters in our text aside from our chosen character.
- Subtracting the number of all other letters aside from our character from the total number of all letters will give us the total for our specified character. Do remember that SUBSTITUTE is a case sensitive formula and will only remove lower case a’s in this example. To tally all the instances of the character regardless of its case use:

**=LEN(A2)- LEN(SUBSTITUTE(UPPER(A2),"a","")) or =LEN(A2)- LEN(SUBSTITUTE(LOWER(A2),"a","")) **

LEN is used for quantifying each character in a string of text in Google Sheets and it will tally all types of instances where a unique character is seen. Thus, it will tally even unnecessary spaces before, after or even in the middle of your text. If we had the text “ How many letters are we using? ”, it would show us a total of 33 since there is one extra space at the beginning of our text and two extra spaces at the end instead of showing 30 (24 letters, five spaces in the middle, and a question mark).

To tally without extra spaces, use the TRIM function before the LEN function in Sheets

TRIM will remove the unnecessary spaces before and after our text and will make two or more spaces in the middle of our text a singular space.

If you don’t want all of your spaces to be included in your total, just use the formula

This will remove all your spaces and leave you with a tally of all the other characters. You can also use the same principle to remove other particular letters or punctuations from the tally just by replacing the second argument inside the SUBSTITUTE formula.

To add up the number of words in Google Sheets, we will have to combine these formulas: LEN, SUBSTITUTE, and TRIM with this formula

This formula works by implementing the following

- TRIM(A2) removes unnecessary extra spaces from your text, if any
- LEN(TRIM(A2)) adds up the total letters and spaces in our trimmed text
- SUBSTITUTE(A2,” “,””) removes all the spaces from your text
- LEN(SUBSTITUTE((A2),” “,””)) quantifies everything else that is not a space
- The result from number four will be subtracted from the result in number two. One is then added to get the total number of words, since the number of words is one greater than the number of spaces.

However this formula will give a value of 1 for instances where your cells are empty, to adjust this, use IF

**=IF(A1="", 0, LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1**)

and that’s the several ways to tally the characters in Google Sheets. Other variations of the functions LEN, SUBSTITUTE, TRIM, and more array formulas will give you unlimited power over quantifying each character or each word from your phrases, sentences, and others as you might need.

If you want to learn how to send an email when a row is added from Google Sheets, we also suggest checking out our detailed guide.

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->