Excel formula to count the Characters in cell based on different criteria

There is a direct Excel formula to count the number of characters in a cell. However, we sometimes come under a situation where we need to count a specific character in a cell or an occurrence of a substring in a cell.  Here are the steps to achieve these requirements.

Counting all the characters in a Cell

To count the number of characters in a cell, there is an Excel function called LEN. 

Syntax of LEN function

=LEN(text)

Examples for using Excel LEN function:

=LEN(A1)- it counts the number of character in Cell A1

=LEN(“Apple”)- counts the number of characters in the word “Apple”

Counting a specific characters in a Cell

To count a specific character in a cell, we need to use substitute and upper functions along with LEN function.

Example:

Let us consider we have a word “Apple” in Range A1 and we need to calculate the number of instances of “P”.

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),”P”,””)) which returns 2.

This formula calculates the number of instances of “P” irrespective of case. If you want this formula to be case sensitive then formula becomes as mentioned below.

 =LEN(A1)-LEN(SUBSTITUTE(A1,”p”,””))

Explanation:

The Formula counts the total number of characters in the cell and subtracts the count of character of the cell excluding the character we need to calculate the count. Here SUBSTITUTE function is used to exclude the character “P”. SUBSTITUTE function is case sensitive; hence we have used “UPPER” function which makes all the characters in the cell to upper case in the first example. 

Counting the occurrences of a substring in a cell

Below example demonstrates on how to calculate the count of a specified string(more than a character) in a cell. 

=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),””)))/LEN(B1)

In this example, the main string is in Cell A1 and the substring which we need to count is in cell B1.  For example. Cell A1 contains “Excel to Excel” and B1 contains “Excel” then the result is 2. The formula is not case sensitive. If you want to the formula to be case sensitive, then remove UPPER function which looks like below.

=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,””)))/LEN(B1).