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"
Google Sheets follows a reverse approach to tally this and a breakdown of this procedure is seen below:
=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
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.
Use our expiration reminder software to easily track due dates from your spreadsheet in just a few clicks.
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.