3

I have a spreadsheet similar to:

            A          B          C
1   "text a1"  "text b1"  "text c1"  
2   "text a2"  "text b2"  "text c2"  
3   "text a3"  "text b3"  "text c3"  
4   "text a4"  "text b4"  "text c4"  

...and I would like to generate a word-count across all rows and columns. How can I do that?

I know a single cell can be counted this way: Count the number of words in a string in Google Spreadsheet, using the SPLIT() function. However, SPLIT() doesn't work when combined with an ArrayFormula() so it can't be used to aggregate multiple rows.

What can I do?

1

First one has to define "a word". I'll use the definition of a word as "a maximal substring of non-whitespace characters", consistent with the linked post.

One can count words in a range by using regexreplace twice:

  1. Replace every word with a single non-whitespace character, such as "a".
  2. Remove all whitespace characters.
  3. Find the length of the resulting string
  4. Add up the lengths.

Here is the formula, in which A1:B4 can be replaced by any other range.

=sum(arrayformula(len(regexreplace(regexreplace(A1:B4,"\S+","a"),"\s",""))))

This approach can be adjusted in a straightforward way to other definitions of word. For example, if a word is defined as a substring of consecutive characters [A-Za-z], then change the character classes accordingly: inner regexreplace deals with what's acceptable in a word, the outer regexreplace deals with the rest.

=sum(arrayformula(len(regexreplace(regexreplace(A1:B4,"[A-Za-z]+","a"),"[^A-Za-z]",""))))
0

After experimenting a bit, this seems like a fine/sneaky approach:

  1. Concatenate all columns together into a single string
  2. Use REGEXPREPLACE() to replace all words into single characters
  3. Use LEN to count how many characters are present to get a word count

So:

=SUM(
  ArrayFormula(
    LEN(
      REGEXREPLACE(A:A & if(isBlank(A:A),""," ") & 
        B:B & if(isBlank(B:B),""," ") & 
        C:C, "[^ ]+[ ]*","x")
    )
  )
)

Does anybody have another way of approaching this?

  • This works okay for three columns; not as convenient if there are twenty (and the formula has to be rewritten based on the number of columns involved). – user79865 Dec 5 '15 at 4:44
  • Good point @NormalHuman! – Jordan Dec 6 '15 at 21:49

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.