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?
In Google Spreadsheets, I would do it a bit different.
=COUNTA(SPLIT(A1, " "))
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:
See following answer on which formulae are unique to Google Spreadsheet:
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 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
This works with blank cells, except when initial cell is blank.
=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.
MMULT(IF(IFERROR(SPLIT(IF(LEN(A3:A), A3:A, ), " "))<>"", 1, 0),
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, ))