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 some times come under a situations where we need to count a specific character in a cell or a occurrence of a substring in a cell.  Here is the steps to achieve these requirements.

Counting all the characters in a Cell: To count the number of characters in a cell, there is a 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 specified 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).
(Visited 321 times, 1 visits today)

Meet the Author

Hi, I am Aniruddha, an avid blogger since 2008 who likes writing about new gadgets, smartphones and excel. You can find them at www.tech2touch.com. You can also follow me on Twitter @tech2touch or my Facebook page tech2touch. You can subscribe to my YouTube channel tech2touch. Enjoy reading and watching!!!

0 comments… add one

Leave a Comment

Shares