How to Count Characters in Google Sheets (Easiest Method!)

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

Count Characters in a Cell or String

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:

‍

1. Click on the cell where you want to display the character count

2. Input =LEN(“text”) or reference your cell inside the parenthesis 

3. Press enter

‍

‍

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. 

Counting Characters in a Range

‍

1. Click on a cell and paste =SUMPRODUCT(LEN(range))

2. Replace "range" in the formula with the range of cells you want to count

3. Click enter to get the character count

‍

‍

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. 

Count A Specific Character Only

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

‍

1. Click on a cell where you want to display the count

2. Paste the formula =LEN(cell number)- LEN(SUBSTITUTE(cell number,"character to count",""))

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",""))

3. Click enter to see the count for your character

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","")) 

Counting Without Extra Spaces

‍

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

=LEN(TRIM(A2))

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.

Counting Without Any Spaces

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

 =LEN(SUBSTITUTE(A2,” “,””))

‍

‍

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.

Counting Words in a Spreadsheet

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

=LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1

‍

This formula works by implementing the following

1. TRIM(A2) removes unnecessary extra spaces from your text, if any
2. LEN(TRIM(A2)) adds up the total letters and spaces in our trimmed text
3. SUBSTITUTE(A2,” “,””) removes all the spaces from your text
4. LEN(SUBSTITUTE((A2),” “,””)) quantifies everything else that is not a space
5. 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.

‍