CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
A12ER
A12ER

Reputation: 13

Counting words in ARRAYS with ARRAYFORMULA in Google Sheets

Counting words with Array formula for every cell won't work.

I have tried to use:

=ARRAYFORMULA(COUNTA(SPLIT(Range, " ")))

=ARRAYFORMULA(SUM(COUNTA(SPLIT(Range," "))))

Both didn't work.

I expected ARRAYFORMULA to count words for every cell in the column and put it next to it, but it counted all the text and put it in one cell instead.

Copy of the sheet (Problem in Cell D123): https://docs.google.com/spreadsheets/d/1pPbJ9k4tiLk8hVxHXRgvu6b4vxJOcgTmyFhD_g8gc_Q/edit?usp=sharing

Upvotes: 1

Views: 762

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(IF(LEN(A3:A), 
 MMULT(IF(IFERROR(SPLIT(IF(LEN(A3:A), A3:A, ), " "))<>"", 1, 0), 
 ROW(INDIRECT("A1:A"&COUNTA(IFERROR(
 QUERY(IF(IFERROR(SPLIT(IF(LEN(A3:A), A3:A, ), " "))<>"", 1, 0), "limit 1", 0)))))^0), ))

0

also could be done by just counting the spaces:

=ARRAYFORMULA(IF(LEN(A3:A), LEN(REGEXREPLACE(A3:A, "[^\s]", ))+1, ))

0

Upvotes: 1

Related Questions