9

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?

migrated from superuser.com Nov 15 '13 at 12:36

This question came from our site for computer enthusiasts and power users.

10

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

enter image description here

Remark

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

  • 1
    For the most part this worked for me (in Google Sheets)... except empty cell returned 1.. So I changed it a little so that empty cells return 0: =COUNTA(SPLIT(A1, " "))-IF(A1 = "",1,0) – CenterOrbit Sep 16 '15 at 20:16
  • We do something similar but it will not work when spaces are not the normal spaces character (e.g. tabs, etc). So I'd love to find a slightly clever way of counting words. – Aldo 'xoen' Giambelluca Jan 20 '16 at 11:37
  • Hi Aldo, best is to simply ask your question here on Web Applications !!! – Jacob Jan Tuinstra Jan 20 '16 at 11:41
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," "))
0

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.

0

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.

0

=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

______________________________________________________________

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy