Excel Function to Count the Number of Instances of Character or Substring in a String
In Excel we can find the Count of Instances or Occurrences of a Character or Substring in a String using the following formula :
=LEN(A1)LEN(SUBSTITUTE(A1,";",""))
Example :
Suppose we have a String in a Cell "A1" which contains the character Semicolon ";"
Now if we want to find how many Semicolons are there in that string , we can find using the below formula :
=LEN(A1)LEN(SUBSTITUTE(A1,";",""))
Please observe the following examples for better understanding :
Tip :
If you want to find the Instance of a Substring(Eg: ",") of length 1 character , we have to use No space ("") in above SUBSTITUTE Function.
In the Same way , If you want to find the Instance of a Substring(Eg: abc) of length 3 characters , we have to use 2 spaces(" ") or a string of length 2 characters(Eg: 12) in above SUBSTITUTE Function.
Similarly ,If you want to find the Instance of a Substring(Eg: abcd) of length 4 characters , we have to use 3 spaces(" ") or a string of length 3 characters(Eg: 123) in above SUBSTITUTE Function.
Thanks ,
TAMATAM
In Excel we can find the Count of Instances or Occurrences of a Character or Substring in a String using the following formula :
=LEN(A1)LEN(SUBSTITUTE(A1,";",""))
Example :
Suppose we have a String in a Cell "A1" which contains the character Semicolon ";"
Now if we want to find how many Semicolons are there in that string , we can find using the below formula :
=LEN(A1)LEN(SUBSTITUTE(A1,";",""))
Please observe the following examples for better understanding :
String

Count of Char/Sub String Formula

Result

ABC;DEF,@@;#@@;JKLM@@;DEF

=LEN(A2)LEN(SUBSTITUTE(A2,";",""))

4

ABC;DEF,@@;#@@;JKLM@@;DEF

=LEN(A3)LEN(SUBSTITUTE(A3,"@@","1"))

3

ABC;DEF,@@;#@@;JKLM@@;DEF

=LEN(A4)LEN(SUBSTITUTE(A4,"DEF","12"))

2

ABC;DEF,@@;#@@;JKLM@@;DEF

=LEN(A5)LEN(SUBSTITUTE(A5,"JKLM","123"))

1

Tip :
If you want to find the Instance of a Substring(Eg: ",") of length 1 character , we have to use No space ("") in above SUBSTITUTE Function.
In the Same way , If you want to find the Instance of a Substring(Eg: abc) of length 3 characters , we have to use 2 spaces(" ") or a string of length 2 characters(Eg: 12) in above SUBSTITUTE Function.
Similarly ,If you want to find the Instance of a Substring(Eg: abcd) of length 4 characters , we have to use 3 spaces(" ") or a string of length 3 characters(Eg: 123) in above SUBSTITUTE Function.
Thanks ,
TAMATAM