In this tutorial, we will learn how to count characters in cells or cell range using 4 effective yet simple ways or methods, We will use the LEN function, SUBSTITUTE function, SUM Function, and helper functions like the LOWER function. We will also learn how to count specific characters in cells or cell ranges with the help of the above functions.
Previously:- Count Blank Cells In Excel
Suppose, you may have come across data loaded with values and separated by spaces, commas, or whatsoever, You may need to count the number of characters in those cells, We will use the above-stated function to resolve the same.
Following is the sample data we will use in this exercise;
Example 1: Count Characters In Cells or Ranges
In this we will use the LEN function to Count characters in cell 2, Therefore, We will input the function as follows;
Now we will select the value i.e. A2 in it and close the parenthesis as follows;
Here we get the answer i.e. 7
Now to get the length of the remaining cells i.e. A3 to A6, we will input the range in the same function as follows;
Now when we press enter, you can see the values of length of each cells is stated next to it not summed up as below;
Now we will use SUM function to sum up all values of length of A2 to A6, to do that we will add the SUM function on B2 just before LEN like below;
As you can see we get the answer i.e. 41, There are 41 characters used in the selected range above.
EXAMPLE 2: Count Specific Character in Cells Or Ranges
In previous example, we have count characters in cells or ranges using simple LEN with SUM Function, now what if you want to count characters specifically in your cell or ranges, for example, you want to find out the number of “a” occurs in A2: A6? we will achieve that in this example using three functions here i.e. SUM, LEN And SUBSTITUTE Function.
The Method:- To count specific character in cells i.e. “a” in our example, first we need to calculate the total length of the cell, and then subtract by the length of all characters except “a”.
Number of “a” = Total Length of a cell – Total Length of Cell excluding the character “a”
To resolve, this first we need to find the total length of cell A2 like we did above;
Now we will subtract the total lenght of cell excluding character “a” with the help of LEN and SUBSTITUTE Function, first we will input the len and substitute function like follows;
Here we have 3 arguments i.e. text, old_text, new_text, we will input the following values into it
text = Cell that contains text value i.e. A2 in our case,
old_text = Text or Character we want to substitute, i.e. “a” in our case,
new_text = Text or character we want to replace, i.e. “” (blank) in our case since we dont want to count a in this case,
Therefore, our formula will be as follows;
=LEN(A2)-LEN(SUBSTITUTE(A2, “a”, “”))
Here we get the answer i.e. 1 which is the number of character “a” in that particular cell.
Now, if we apply the same formula to the range i.e. A2:A6 by editing the formula like before;
=LEN(A2:A6)-LEN(SUBSTITUTE(A2:A6, “a”, “”))
Here as you can see we get the value like above in ranges as it didnt add up like below;
Now like we did above, we will sum up the formula by adding the SUM function to it like below;
=SUM(LEN(A2:A6)-LEN(SUBSTITUTE(A2:A6, “a”, “”)))
The answer we get is 3, Three times character “a” occurs in our range;
You can practice more by finding out the number of character “e” occurs in your range of cells and do put the answer below in comment box.
So here we have learnt how to count characters in cells or ranges and also how to count characters specifically i.e. “a” in the example above.