In this tutorial, we will learn to write different formulas in Excel to count the number of characters from a cell or a range of cells.
Count Characters from a Single Cell
To count characters from a cell, you need to enter the LEN function in the cell in which you want to get the count. After that, refer to the cell (A1). In the end, hit enter to get the count. If you have multiple cells you can apply the same formula for those cells as well. See the example below:
=LEN(B1)Count Characters without Spaces
If you have a space or multiple spaces in a cell, in that case, you can use the following formula where we have used a combination of LEN and SUBSTITUTE.
=LEN(SUBSTITUTE(A1," ",""))In this formula, we have used SUBSTITUTE to replace the space that you have between the “Hello” and “World!” with a blank value.
Count Characters from a Range
If you have a range of cells and you want to count characters from that entire range you can use a combination of SUMPRODUCT and LEN.
=SUMPRODUCT(LEN(A1:A3))When you use SUMPRODUCT it can take an array within a single cell. In this way, it takes the value of all the cells (3) from the range. And in between SUMPRODUCT, LEN gets the count of characters.
If you have a cell or multiple cells where you have a space within the value and you don’t want to count that space while counting the characters. You need to create a combination of SUMPRODUCT, LEN, and SUBSTITUTE.
In the above example, we have the count 36 when you include space while getting the count of characters. But when we use the formula (SUMPRODUCT, SUBSTITUTE, and LEN).
=SUMPRODUCT(LEN(SUBSTITUTE(A1:A3," ","")))Count a Specific Character from a Cell or a Range
Let’s say you want to get the count of a specific character from a cell, you can do this by combining LEN and SUBSTITUTE.
In the above example, we have used the formula to count the asterisk (*) from the cell.
=LEN(A1)-LEN(SUBSTITUTE(A1,"*",""))
This function works in two parts:
- In the first part, you have the LEN function that gets the count of total characters from the cell.
- In the second part, you have SUBSTITUTE which replaces the (*) with no value. After that, LEN returns the count of characters without an asterisk.
In the end, you have two counts one is with (*), and one is without (*). And when you deduct both, you get the count of (*) in the result.
And in the same way, if you want to count a specific character from a range of cells and you need to wrap the above formula within the SUMPRODUCT function and refer to the entire range instead of the single cell.
=SUMPRODUCT(LEN(A1:A7)-LEN(SUBSTITUTE(A1:A7,"*","")))
When you use SUMPRODUCT, it takes the entire range as an array and uses the formula for the entire range instead of a single cell. That means:
In the first, you get the total count of the characters from the entire range, and in the second part, SUBSTITUTE, remove the (*) and LEN gets the count. And after that, deducting both counts, you get the count of the specific character in the result.