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




No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts