0

I have a very specific question

There is a native vba function that counts how many time a text (such as a word or even a single character) repeats inside other text (string)?

It would get as parameters two string values, for example, and return a integer/long value.

I looked among worksheet functions and I couldn't find any. Should I make my own one?

Thanks very much!

Claudio Augusto Pereira Rolim
  • 491
  • 8
  • 8
  • possible duplicate of [Count specific character occurrences in string](http://stackoverflow.com/questions/5193893/count-specific-character-occurrences-in-string) – nicholas79171 Jul 08 '15 at 00:14
  • 1
    Subtract the length of substituting the text out from the length of the original and divide the result by the length of the text. `=(LEN("Text and More Text, Text of all Texts")-LEN(SUBSTITUTE("Text and More Text, Text of all Texts", "Text", "")))/LEN("Text")` = 4. –  Jul 08 '15 at 00:28

1 Answers1

1

As it doesn't exist in vba, you should make your own. You can write a loop using InStr, counting the number of iterations. It should be pretty performant as it essentially won't have to do any memory allocations, and VBA's string functions are fast.

Erik Eidt
  • 23,049
  • 2
  • 29
  • 53