formulasgoogle sheets
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.
With the following Google Apps Script, you can count the way you want to, in a Google Spreadsheet.
/**
* myWords(searchRange, checkRange)
* @param {Array.Array} searchRange
* @param {Array.Array} checkRange
* @customfunction {int}
*/
function myWords(searchRange, checkRange) {
// small error handling
if(searchRange[0].length > 1 || checkRange[0].length > 1) {
throw "one of the ranges has more than one column";
return;
}
// counting of words
var output = [];
for(var k=0, kLen=searchRange.length; k<kLen; k++) {
var count = 0;
var cell = searchRange[k][0];
for(var i=0, iLen=checkRange.length; i<iLen; i++) {
var spl = checkRange[i][0].split(" ");
if(spl.length == 1) {
if(spl == cell) {
count++;
}
} else {
for(var j=0, jLen=spl.length; j<jLen; j++) {
if(spl[j] == cell) {
count++;
}
}
}
}
output.push([count]);
}
// return individual outcomes
return output;
}
This piece of code will also work in the new Google Spreadsheet. Paste the code in the script editor, under Tools>Script editor. Press the save button and you can start using the script, as shown in the screenshot.
There is no need of something similar to VBA as the percentage could be calculate by using built-in functions.
The column C has values L or W. To calculate the percentage of W use the following formula in C1:
=COUNTIF(C3:C,"W")/COUNTA(C3:C)
COUNTIF(C3:C,"W") counts cells from C3 to the last cell in column C with W.COUNTA(C3:C) counts all the cells from C3 to the last cell in column C with any alphabetic value. To display the result as percentage apply the % format.
Best Answer
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
OR (if in cell
A7we 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
heroesorHeroIf you want to include variations of
hero, likeHeroorHeroesyou can use:If on the other hand want just the word
Heroor 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
A7turning the cell depending formula toFunctions used:
REGEXREPLACEArrayFormulaCONCATENATESPLITJOINCOUNTIF