In this article, we’ll show you several methods to count characters in Google Sheets for your data entry or inventory needs.
Counting Characters in a Cell or String
LEN is the Google Sheets function for text length and will return the number of characters within your specific cell or string. Follow the simple steps below:
- Click on the cell where you want to display the character count.
- Enter
=LEN("text")or reference your cell inside the parentheses. - Press Enter.
When using a specified string inside the formula, remember to enclose it in quotation marks.
Note: When using this formula, it counts each individual part of your string, including spaces, numbers, and even punctuation. It will also only work for a single cell. If you want to apply it to an entire range, drag your formula to the adjacent cells of your other texts and manually sum up those values or follow the instructions below.
Counting Characters in a Range
- Click on a cell and paste
=SUMPRODUCT(LEN(range)). - Replace “range” in the formula with the range of cells you want to count.
- Press Enter to get the character count.
This treats your range as an array inside the SUMPRODUCT function and sums up the number of characters for each cell inside the array. You can use this for multiple arrays as well! Just make sure to use a valid range for your SUMPRODUCT array function to work correctly.
Counting a Specific Character Only
To quantify a specific unique character from a string, follow the steps below.
- Click on a cell where you want to display the count.
- Paste the formula
=LEN(cell reference) - LEN(SUBSTITUTE(cell reference, "character to count", "")).
Replace “cell reference” with the cell you want to count. In our case, we’re using cell A2. Replace “character to count” with the character you want to count. In our example, we want to count the occurrences of the character “a”.
=LEN(A2)- LEN(SUBSTITUTE(A2,"a",""))
- Press Enter to see the count of your character.
Google Sheets follows an inverse approach to count this, and an explanation of this procedure is presented below:
- LEN(A2) adds up the total in our original text in cell A2.
- SUBSTITUTE((A2,”a”,””)) removes our specified character (in this case, “a”) from the text string.
- LEN(SUBSTITUTE(A2,”a”,””)) totals the number of all other characters in our text, excluding our chosen character.
- By subtracting the count of all other letters from our character’s count from the total count of all letters, we get the total for our specified character. Remember that SUBSTITUTE is a case-sensitive formula and only removes lowercase “a” in this example. To count all occurrences 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 to quantify each character in a text string in Google Sheets, and it counts all types of occurrences where a single character is visible. So, it even counts 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 display a total of 31 because there is an extra space at the beginning of our text and two extra spaces at the end instead of showing 28 (24 letters, five spaces in the middle, and one question mark).
To count without extra spaces, use the TRIM function before the LEN function in Sheets:
=LEN(TRIM(A2))
TRIM will remove unnecessary spaces before and after our text and turn two or more spaces in the middle of our text into a single space.
Counting without Any Spaces
If you don’t want to include all your spaces in your total, simply use the formula:
=LEN(SUBSTITUTE(A2," ",""))
This will remove all your spaces and leave you with a count of all other characters. You can also use the same principle to remove other specific letters or punctuation marks from the count by replacing the second argument inside the SUBSTITUTE formula.
Counting Words in a Spreadsheet
To count the number of words in Google Sheets, we’ll need 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 steps:
- TRIM(A2) removes any unnecessary extra spaces from your text, if any.
- LEN(TRIM(A2)) tallies the number of letters and spaces in our modified text.
- SUBSTITUTE(A2, ” “, “”) removes all spaces from your text.
- LEN(SUBSTITUTE((A2), ” “, “”)) quantifies everything that is not a space.
- The result of the fourth step is subtracted from the result of the second step. Then one is added to get the total number of words because the number of words is one more than the number of spaces.
However, this formula will give a value of 1 for cases where your cells are empty. To adjust for this, use IF:
=IF(A1="", 0, LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)
These are the different ways to count characters in Google Sheets. The various variations of the LEN, SUBSTITUTE, TRIM functions, and other array formulas will give you unlimited power to quantify every character or word in your sentences, phrases, etc., as you may need.
Use our expiration reminder software to easily track due dates in your spreadsheet with 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.
