Count word occurrences in survey feedback

Question

I have a suggestions column in a survey feedback, that's being pushed from the survey provider to a Google Spreadsheet. I'm trying to do a bit of wording analysis on it. For example, I'm trying to count the number of occurrences of the word "Difficult".

I already found a way by concatenating all the feedback and splitting it into cells and then comparing the cells with the word, but this is not scalable. It looks like Google has a limit on the amount of words I can squish into one cell and so the concatenate function is giving me an error. I'm hoping to use the SEARCH or FIND function, but I can't seem to figure out how to count the words in the column.

Here's an example of what I'm trying to do: counting of words

Answer 1

With the following Google Apps Script, you can count the way you want to, in a Google Spreadsheet.

Code

/**
 * 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;
}

Screenshot

Note

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.

Answer 2

Might you be able to do this in Docs instead? For a single word, say Difficult you might Find and Replace... with say xyzyx it shows the number of replacements and then undo.

For a collection of words (not phrases) the Word Cloud Generator add-on from Learn in 60 seconds may be helpful (clicking Append top word counts in the 'New' version). It seems it may ignore many single instance words and those of fewer than five characters but for some purposes that might be an advantage. Does treat punctuation attached to a word as separate from the word alone but that could be handled by replacing commas and full stops.