CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulascountinggoogle-sheets-query
JOY
JOY

Reputation: 389

Count occurrences of a specific word in Google Spreadsheet

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

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

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

Edited: 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?

Upvotes: 3

Views: 8673

Answers (3)

Tom
Tom

Reputation: 765

If you want to count occurences of "Hero" word

=COUNTIF(SPLIT(JOIN(" ", B1:B3), " -."&CHAR(10)), "Hero")

Where:

  • B1:B3: cells with text
  • "Hero": the word to count

Explaination

  1. JOIN(" ", B1:B3): Concatenation of all cells with text
  2. SPLIT(..., " -."&CHAR(10)): Create an array with each words
  3. COUNTIF(..., "Hero"): Count each array item equals to "Hero"

Example

if input text is: 

Hero Hero-666 heroes heroic

➔ then formula will return 2.

If you want to count occurences of "Hero" string

(Even nested in an other word, i.e: "Heroes")

=COUNTA(SPLIT(UPPER(JOIN(" ",B1:B3)), "HERO", false, false))-1

Where:

  • B1:B3: cells with text
  • "HERO": the string to count

Explaination

  1. JOIN(" ", B1:B3): Concatenation of all cells with text
  2. UPPER(...): Convert text in upper case
  3. SPLIT(..., "HERO"): Split on each occurences of the string
  4. COUNTA(...)-1: Count how many splits have been done

Example

if input text is: 

Hero Hero-666 heroes heroic

➔ then formula will return 4.

Upvotes: 7

marikamitsos
marikamitsos

Reputation: 10573

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 the following:

Case insensitive for any language formula: 

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

You can even have *heRO* placed in a cell like A7 and use

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

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

It also works for any language (including German).

Upvotes: 1

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(COUNTA(IFERROR(SPLIT(QUERY(SUBSTITUTE(
 UPPER(B1:B3), UPPER(A5), "♦"),,99^99), "♦")))-1)

and for german:

=ARRAYFORMULA(COUNTA(IFERROR(SPLIT(QUERY(SUBSTITUTE(
 UPPER(C1:C3), "HELD", "♦"),,99^99), "♦")))-1)

Upvotes: 0

Related Questions