In this tutorial, we will discuss the count of occurrences of a character or a word in a particular string and string range in Excel. Often, we have to calculate the frequency of the character in a cell or a data range containing a large amount of data. So, to make this counting number easier, we will discuss some easy-to-use formulas in this article.
1. Using SUMPRODUCT and LEN Functions to Count Occurrences of Character in a String
If you want to know the total number of characters in a cell, the LEN function serves the purpose. But, when you have to know the total numbers of characters in a range, you can combine the SUMPRODUCT and LEN functions. For example, we have a book name dataset and we want to know the total count of characters in the range. So, we will use the following steps.
Steps:
- First, type the below formula:
=SUMPRODUCT(LEN(B5:B9))Here, the LEN function returns the number of characters in a text string and the SUMPRODUCT function returns the sum of the products of corresponding range.
- And, here is the total character count in the specified range.
Note:
Excel LEN function counts all characters in each cell, including letters, numbers, spaces, symbols, and all spaces, punctuation marks, etc.
2. Combine SUBSTITUTE and LEN Functions to Sum Up Occurrence of Specific Character in a String in Excel (Case Sensitive)
Sometimes, we need to know the count of the frequency of a character in an Excel cell. In a situation like this, we can use the LEN and SUBSTITUTION functions simultaneously. The thing is, the SUBSTITUTE function is case sensitive so if you look for small letter characters, you will only get the count of those characters. For instance, in our book name dataset, we will only look for the count of character ‘a’. So, the steps involved are:
Steps:
- Firstly, type the following formula:
=LEN(B5)-LEN(SUBSTITUTE(B5,"a",""))
Breakdown of the Formula:
➤ LEN(B5)
Here, the LEN function counts the characters in Cell B5.
➤ SUBSTITUTE(B5,”a”,””)
The SUBSTITUTE function replaces all the ‘a’ characters with blank (“”).
➤ LEN(SUBSTITUTE(B5,”a”,””))
Now, the SUBSTITUTE formula is covered with the LEN function that gives the count of the rest of the characters of the string (excluding all ‘a’).
➤ LEN(B5)-LEN(SUBSTITUTE(B5,”a”,””))
Finally, this formula subtracts two previously calculated lengths and gives the total count of our specified character ‘a’.
- In the end, here is the count of the expected character:
3. Use SUBSTITUTE and LEN Functions to Calculate Occurrence of Specific Character in a String in Excel (Case Insensitive)
On the contrary to the previous method, you may need to count the characters irrespective of their case sensitivity. In that case, you can add the UPPER or LOWER function to the previous formula. Now, we will look for both ‘A” and ‘a’ in our book name dataset. Here are the steps:
Steps:
- Type the below formula at first.
=LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),"A",""))Here, the UPPER function converts a text string to all uppercase letters. Here all ‘a’ will be converted to ‘A’ and you will get the count accordingly. And, the rest of the formula works in a similar way explained in the previous example.
- Finally, you will get the following result, if the formula is entered properly.
4. Calculating the Number of Occurrences of a Single Character in a Range in Excel
Sometimes you may need to calculate the total occurrence of specific characters in a string range. Such as, we want to know the frequency of character ‘A’ or ‘a’ in a data range.
Steps:
- Type the following formula firstly.
=SUM(LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,"a","")))Here, the SUM function adds all the numbers in a range of cells. The rest of the formula works as before and finally returns the total count of character ‘a’ in the whole range.
- Consequently, you will get the total count as follows:
5. Counting the Number of Occurrences of a Text or Substring in a Range in Excel
Similar to the previous method, you might want to know the frequency of a specific text in a data range. In such situations, you can use combinations of functions. In this method, we combine SUM, LEN, and SUBSTITUTE functions. For example, we have a data range containing color names and we want to know the frequency of the color ‘Green’ from the range.
Steps:
- Type the below formula at first.
=SUM(LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,"Green","")))/LEN("Green")In the above formula, you need to divide the character count by the specified text/substring Otherwise, each character in the text will be counted individually.
- Lastly, here is the total count of ‘Green’ in the specified range.
Note:
The above-mentioned formula must be entered as an array formula. To enter a formula as an array in Excel for Windows, press CTRL+SHIFT+ENTER to enter a formula as an array.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In the above discussion, I have tried to explain easy and suitable methods to find the character count in a string or range of strings in Excel. Hopefully, you will find all the methods helpful. If you have any queries regarding these methods, let me know.
