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:
When you use LEFT(…)-1, you’re sustracting one character each ocurrence of the substring (each time you find this substring)
=LEN(A3)-LEN(SUBSTITUTE(A3,B3,LEFT(B3,LEN(B3)-1)))
So you can use also this
=(LEN(A3)-LEN(SUBSTITUTE(A3,B3,””)))/LEN(B3)
Because you “erase” as many characteres as ocurrences*length of the substring happens, so if you divide by this legth of the substring, you get the number od occurrences.