Excel Count Occurrences of Character in String

Get FREE Advanced Excel Exercises with Solutions!

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.


Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.


5 Methods to Count Occurrences of Character in String in Excel

1. Find Total Count of Character Occurrences in String in Excel using SUMPRODUCT and LEN Function

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.

Find Total Count of Character Occurrences in String in Excel using SUMPRODUCT and LEN Function

  • 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",""))

Combine SUBSTITUTE and LEN Functions to Sum Up Occurrence of Specific Character in a String  in Excel (Case Sensitive)

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 Combination of  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.

Use Combination of  SUBSTITUTE and LEN Functions to Calculate Occurrence of Specific Character in a String  in Excel (Case Insensitive)

  • Finally, you will get the following result, if the formula is entered properly.


4. Calculate 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.

Calculate the Number of Occurrences of A Single Character in a Range in Excel

  • Consequently, you will get the total count as follows:


5. Count Number of Occurrences of Character (A Text or Substring)  String in Range

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.

Count Number of Occurrences of Character (A Text or Substring)  String in Range

  • 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.


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.

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo