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:**

## One Response

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.