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

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

``````=(LEN(B1)-LEN(SUBSTITUTE(UPPER(B1),UPPER(A5),"")))/LEN(A5)
``````

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. – Rubén May 23 '20 at 3:43
• I updated my question. – JOY May 23 '20 at 7:12

### EDIT

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

``````=COUNTIF(SPLIT(CONCATENATE(B1:B3), " "), "*heRO*")
``````

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

``=COUNTIF(SPLIT(CONCATENATE(B1:B3), " "), A7)``

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

It also works for German or any language.

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:

``````=COUNTIF(SPLIT(JOIN(" ", SPLIT(CONCATENATE(ArrayFormula(REGEXREPLACE(
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: