A
A
Alina Shah2021-08-03 05:33:50
Google Apps Script
Alina Shah, 2021-08-03 05:33:50

How to count how many occurrences of each word in a table (or cell)?

https://docs.google.com/spreadsheets/d/1RrMOuBr-Eq...
There is a set of reviews, I want to determine how many words are found.
Those. not certain words, but count all the words - how many each of them occurs.
Is there such a universal formula?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
G
Grigory Boev, 2021-08-04
@Shah_Alina

Victor L was right.
If you change its code a little, you can get not by one cell, but by range.

function COUNTUNIQWORDS(range) {
 let wordsCount = {};
 range.forEach(row=>{
   row.forEach(cell=>{
     let text = String(cell)
                  .toLowerCase()
                  .replace(/[^а-яА-ЯёЁa-zA-Z ]+/gi, ' ')
                  .replace(/\s+/gi, ' ');
    let words = text
                  .split(' ')
                  .filter(word=>word!=='');
    words.forEach(word=>{
      if (wordsCount[word]){
        wordsCount[word]+=1;
      }else{
        wordsCount[word]=1;
      };
    });
   });
 });
 
  let result = Object.keys(wordsCount)
                .sort()
                .map(word=>[word, wordsCount[word]]);
  return result;
};

Demo table with code

V
Victor L, 2021-08-03
@Fzero0

You can create your own formula, for example

function COUNTUNIQWORDS(cell) {
 var result = []
 var words = String(cell).split(" ");
 var wordsCount = words.reduce(function (acc, w) {
  if(acc[w]) {
    acc[w] += 1
  } else {
    acc[w] = 1;
  }
  return acc;
}, {});
for (var w in wordsCount) result.push('Cлово "'+ w +'" встречается '+ wordsCount[w] +  ' раз');
return result
}

6108ef31e255c656419246.png

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question