Counting characters in Excel cells is quite simple when there are no extra conditions. As soon as they appear, you may need compound formulas and some extra knowledge. Let’s go step by step.
All examples use four cells in A2:A5: “red apple”, “banana”, “kiwi” and “Avocado salad”.
Count characters in each cell of a column
To count all characters in one cell, including spaces, use the text function LEN:
=LEN(A2)
Fill it down the column, and then sum the results with =SUM(B2:B5) to get the number of characters in all cells: 32.
Count characters in a range with one formula
The solution above needs a separate column. Can you do without it? Yes, with an array formula:
=SUM(LEN(A2:A5))
What happens here, in two steps:
- LEN, applied to a range of cells, creates an array of the lengths of each cell in memory;
- SUM adds up these values and returns the result.
In Excel 2019 and earlier, this formula, like all array formulas, must be entered with Ctrl+Shift+Enter. Otherwise it returns only the length of the first cell, because the array is not created. In Excel 365 and 2021, a regular Enter is enough.
To avoid thinking about the version at all, use SUMPRODUCT: it handles arrays by itself in any Excel version.
=SUMPRODUCT(LEN(A2:A5))
Count characters with conditions
Without spaces
Remove the spaces with SUBSTITUTE before measuring the length. The result for the example is 30:
=SUMPRODUCT(LEN(SUBSTITUTE(A2:A5," ","")))
A specific character
How many times does the letter “a” occur in the range? Subtract the length without this letter from the full length:
=SUMPRODUCT(LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,"a","")))
SUBSTITUTE is case-sensitive, so the capital “A” in “Avocado” is not counted, and the result is 7. To count both cases, convert the text to lowercase with LOWER first, and the result is 8:
=SUMPRODUCT(LEN(A2:A5)-LEN(SUBSTITUTE(LOWER(A2:A5),"a","")))
The same formula counts a whole word or substring if you divide the result by its length: for “an”, add /LEN(“an”) at the end.
Cells longer than N characters
How many cells are longer than 6 characters (2 in the example), and what is the maximum length (13)?
=SUMPRODUCT(--(LEN(A2:A5)>6))
=MAX(LEN(A2:A5))
The second formula is also an array formula: in older versions, confirm it with Ctrl+Shift+Enter. To check cells against a length limit, such as 30 characters for ad headlines or 60 for page titles, see how to find cells by condition.
Count characters with !SEMTools
With formulas, you have to remember the syntax, type the formula, fill it down and then delete the helper column. In my !SEMTools add-in, you can count characters in cells in a couple of clicks: select the column, and the length of each cell appears right next to it. The same menu counts specific characters, letters, digits and words.
Count characters, letters, digits or words in whole columns right in place, without formulas.
!SEMTools solves this and hundreds of other text tasks in a couple of clicks!
Summary
I hope you found what you were looking for on this page. LEN counts the characters in one cell, SUM(LEN()) or SUMPRODUCT(LEN()) in a whole range, and SUBSTITUTE adds conditions: without spaces or only a specific character.
Learning array formulas, part 1 of 4: how to create an alphabet in Excel.
Learning array formulas, part 3 of 4: extract text before the first digit in a cell.