## Wednesday, 24 December 2014

### How to Find the Count of Instances or Occurrences of a Character or Substring in a String

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 :

 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