Count the number of words in a string in Google Sheets

Question

I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?

Answer 1

In Google Spreadsheets, I would do it a bit different.

Formula

=COUNTA(SPLIT(A1, " "))

Explained

The SPLIT function is only available in Google Spreadsheet and will split the cell's content on every space (" "). The COUNT function will simply count the instances.

The Excel formula gives the same answer, but a bit more laborious:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Screenshot

Remark

See following answer on which formulae are unique to Google Spreadsheet:
https://webapps.stackexchange.com/a/44719/29140

Answer 2

Slight improvement to formula in this other answer so in case that A1 is blank or if it has an empty string it will return zero.

=IF(LEN(A1)=0,0,COUNTA(SPLIT(A1," "))

Answer 3

If you want to do this on a cell range (i.e. A1:A25) you can use the formula above, where CHAR(32) is the space character " "

=COUNTA(SPLIT(ARRAYFORMULA(CONCATENATE(A1:A25&CHAR(32)));CHAR(32)))

This works with blank cells, except when initial cell is blank.

Answer 4

Another:

=COUNTA(SPLIT(TRIM(A1&" #")," "))-1

This just appends a space and pound sign to the end of whatever's in the target cell, including adding it to a null, so there are never any errors throwing off the count. Then we're just subtracting that temporary "fake space" back out at the end.

Answer 5

=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), ))

______________________________________________________________

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