0

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

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

enter image description here

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.