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.




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:



enter image description here


See following answer on which formulae are unique to Google Spreadsheet:

| improve this answer | |
  • 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

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," "))
| improve this answer | |

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.

| improve this answer | |


=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.

| improve this answer | |

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





| improve this answer | |

Your Answer

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