I have some cells with text. I need to count the occurrences of a specific word (not a list) from those cells.

Example sheet:https://docs.google.com/spreadsheets/d/1WECDbepLtZNwNfUmjxfKlCbLJgjyUBB72yvWDMzDBB0/edit?usp=sharing

So far I found one way to count it in English by using SUBSTITUTE to replace all these words with "":


However, I don't know why but it doesn't work in German.

  • Please add a brief description of your search/research efforts as is suggested in How to Ask. Commented May 23, 2020 at 3:43
  • 1
    I updated my question.
    – JOY
    Commented May 23, 2020 at 7:12

1 Answer 1



I just realized that you have cross-posted on both sites.
I also realized that there is a much simpler case insensitive formula for your needs


OR (if in cell A7 we have *HeRo*)


If you want just the word Hero, remove the asterisks * around it.

It also works for German or any language.

Original answer

In your sheet you mention that the count should be 14.
Considering that, I believe you are looking for a solution to also include words like heroes or Hero

If you want to include variations of hero, like Hero or Heroes you can use:

     B1:B3,"(([H|h]ero).[a-z]\b)|([H|h]ero)"," @ ♜ "))),char(10))), " "), "♜")

If on the other hand want just the word Hero or just some cell value you can omit part of the regex "(([H|h]ero).[a-z]\b)|([H|h]ero)" which would now be

"([H|h]ero)" or just A7 turning the cell depending formula to

=COUNTIF(SPLIT(JOIN(" ", SPLIT(CONCATENATE(ArrayFormula(REGEXREPLACE(B1:B3,A7," @ ♜ "))),char(10))), " "), "♜")

Functions used:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.