Have you ever wondered how to count the number of characters, string, or words in a cell or multiple cells in Excel? In this blog, we would unlock this technique and also learn some of the other variations that can be performed using this trick. We would unlock the technique of counting the words in cell or range of cells in Excel and much more interesting things, like specific-character count, etc.
This is totally a formula-based technique. Here we would learn how to use different formulas in combination to get the required result.
Let us go step by step from simple to advance one.
Count Number of Characters in Cell in Excel
Excel has provided with an in-built simple formula to count the number of characters in the one cell in Excel named LEN formula.
Note that, for Excel, a space character is also a character. So one space would be counted as one character. In a similar way, it considers numbers, letters, symbols, punctuation marks, etc. while performing character count in Excel.
There is only one attribute of this formula which is the reference of the cell for which you want to perform character count in Excel.
The below image is self-explanatory.
Count Characters in Multiple Cells
When it comes to counting the number of characters in a cell range, the very first thing that comes in anybody’s mind is to count the individual character count and then sum it up, like the way shown in the image below:
Or you may think of using a formula like this – =LEN(B2)+LEN(B3)+LEN(B4)+LEN(B5)
The above way seems to be easy, but no one would dare to use it when you have thousands of such cells.
In this situation, the SUMPRODUCT function along with the LEN formula is a life-saver.
In a simple way, the SUMPRODUCT function finds the total of the products of values in multiple cells in Excel.
Use the following compact formula to find total character count for multiple cells in Excel.
Explanation of the above formula – The LEN formula counts the number of characters in a cell in Excel. When a range is specified as an attribute for LEN, it makes an array of the character count of individual cells in the range. Finally, the SUMPRODUCT function sums up the values in this array.
Count Number of Words in Cell In Excel
In the above section, we learned the LEN formula that counts the number of characters in a single cell in Excel. In this section, we would go one step ahead and unlock the technique to count the total number of words in a cell(s) in Excel.
Surprisingly, there is no direct formula to achieve this. However, we can make a compact formula (formula within a formula) to get the required result.
Use this formula in cell B2 =LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))+1
The result of the above formula would be as below:
Explanation of the Formula. – The LEN(A2) in this formula counts the number of characters in cell A2 (which comes to 44). The LEN(SUBSTITUTE(A2,” “,””)) removes the space characters from the text in cell A2 and then finds the text length (which comes to 37). So, subtraction value comes to 7 (i.e. 44 – 37). Finally, 1 is added to this value as the number of words in the cell is more than the number of space characters by the value of one.
To find the sum total of all the words in the range of cells A1:A5, simply use the above formula nested into SUMPRODUCT function:
Count Specific Characters in Cell in Excel
Sometimes, you may have a requirement to count the occurrence of only a particular character in a cell in Excel. To achieve this, the SUBSTITUTE function used along with the LEN function would be helpful.
In the below image, you can see the list of different materials bought by different customers. Now, suppose you want to find the count of “C” code materials sold to each of the customers.
To achieve this, enter the below formula in cell C2 (and other cells in column C).
Explanation of the Formula – The LEN(B2) finds the total number of characters in the cell B2 (which comes to 42). The LEN(SUBSTITUTE(B2,”C”,””) firstly, replaces the character “C” with a blank and then finds the length of this replaced string (which comes to 40). Finally, they are subtracted (42 – 40) to get the count of the replaced character “C” in the cell B2.
It is important to note that the above that the SUBSTITUTE formula is a case-sensitive formula, which means that the excel would only substitute “C” (the one in upper case), and not the “c” (in lower case). Therefore, before using this formula, you need to make sure the structure of your data.
So if in case, your data contains any lower case character, then firstly, you need to convert it into upper case and then apply the formula. The Formula, in that case, would look like-
The UPPER(B2) would firstly, convert the entire string in cell B2 into upper-case, and the rest formula is the same.
Count Length of Certain String in Range of Cells
To find the count of certain characters in a particular range of cells (multiple cells), we can nest the above formula (learned in the previous section) into the SUMPRODUCT function, as shown below:
The result comes to “7”.
This brings us to the end of this blog. Share your views and comments in the comments section below.