title: Count occurrences of a specific word in Google Spreadsheet
body: <p>I have some cells with text. I need to count the occurrences of a specific word (not a list) from those cells.</p>
  
  <p>Example sheet:<a href="https://docs.google.com/spreadsheets/d/1WECDbepLtZNwNfUmjxfKlCbLJgjyUBB72yvWDMzDBB0/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1WECDbepLtZNwNfUmjxfKlCbLJgjyUBB72yvWDMzDBB0/edit?usp=sharing</a></p>
  
  <p>So far I found one way to count it in English by using SUBSTITUTE to replace all these words with "":</p>
  
  <pre><code>=(LEN(B1)-LEN(SUBSTITUTE(UPPER(B1),UPPER(A5),"")))/LEN(A5)
  </code></pre>
  
  <p>However, I don't know why but it doesn't work in German.</p>
  
  <p><strong>Edited:</strong>
  I don't want to count "Hero" in "Heroes". However, I'd like to count "afk" in "AFK-Spiel" (German for example). Is it possible?</p>