Excel Formula to count the occurrences of text, characters, or words in a cell

In Excel we can find the Count of Occurrences of a Character or Sub-string in a String using the following formula :

=LEN(Text)-LEN(SUBSTITUTE(Text,Search,Replace))

Example :

To find how many ‘t’s are in word ‘throttle’, we can find with below formula
=LEN(“throttle”)-LEN(SUBSTITUTE(“throttle”,”t”,””))
which will result ‘3’

Download example file: Count the Number of Instances of Character in a String.xls

Observe the following examples for better understanding :

Complete Text Search for String Count of instances Formula (Manual)
Can you find the string count in the given strings of words t 5 =LEN(A12)-LEN(SUBSTITUTE(A12,”t”,””))
Can you find the string count in the given strings of words in 4 =LEN(A13)-LEN(SUBSTITUTE(A13,”in”,”I”))
Can you find the string count in the given strings of words the 2 =LEN(A14)-LEN(SUBSTITUTE(A14,”the”,”th”))
Can you find the string count in the given strings of words c 1 =LEN(A15)-LEN(SUBSTITUTE(A15,”c”,””))
Can you find the string count in the given strings of words C 1 =LEN(A16)-LEN(SUBSTITUTE(A16,”C”,””))
Can you find the string count in the given strings of words string 2 =LEN(A17)-LEN(SUBSTITUTE(A17,”string”,”strin”))

In above formula structure, you have to enter the parameters manually, this kind if formula will suit if you have only 1 or 2 strings to search, and the same will be be useful if you have too many search strings.

Check the below formula.

Complete Text Search for String Count of instances Formula (Automatic)
Can you find the string count in the given strings of words t 5 =LEN(A3)-LEN(SUBSTITUTE(A3,B3,LEFT(B3,LEN(B3)-1)))
Can you find the string count in the given strings of words in 4 =LEN(A4)-LEN(SUBSTITUTE(A4,B4,LEFT(B4,LEN(B4)-1)))
Can you find the string count in the given strings of words the 2 =LEN(A5)-LEN(SUBSTITUTE(A5,B5,LEFT(B5,LEN(B5)-1)))
Can you find the string count in the given strings of words c 1 =LEN(A6)-LEN(SUBSTITUTE(A6,B6,LEFT(B6,LEN(B6)-1)))
Can you find the string count in the given strings of words C 1 =LEN(A7)-LEN(SUBSTITUTE(A7,B7,LEFT(B7,LEN(B7)-1)))
Can you find the string count in the given strings of words string 2 =LEN(A8)-LEN(SUBSTITUTE(A8,B8,LEFT(B8,LEN(B8)-1)))

Bonus Tip :
To achieve best results, In formula SUBSTITUTE(Text,Search,Replace) the length of string ‘Replace’ shall be 1 character less than ‘Search’ string
Eg,
Search = “A”, then Replace = “” (means replace char A with Nothing)
Search = “AB”, then Replace = “A”
Search = “ABC”, then Replace = “12”
Search = “12345”, then Replace = “ABCD”
Here we are worried about the ‘Search’ string only, you can ‘Replace’ with any characters (make sure that the length is 1 char less). I have used LEFT(LEN()) formula for the very same purpose.

Download example file: Count the Number of Instances of Character in a String.xls

Also read:


1

One Response

Show all responses
  1. Vidal Martinez Garcia
    October 30, 2016

Leave a Reply