Count Words in EXCEL Cell
To count the number of words in a cell use the function LEN(), TRIM() and SUBSTITUTE().
Let the cell A1 contains the text "I Wish you a nice time!".
For the calculation in her words, we write the formula (see sample file)
=IF(LEN(TRIM(A1))=0,0, LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)
the Formula returns the value 4, signifying that the cell contains four words separated by spaces. It does not matter if the words are separated by several spaces or words begin or end with a space. The TRIM() removes extra spaces, spaces at the beginning and at the end of the text in the cell.
Also, the formula checks whether there is useful information in the cell: LEN(TRIM(A1))=0.