Count Number of Words in a Cell

Generic formula 

=LEN(TRIM(CELL))-LEN(SUBSTITUTE(Cell," ",""))+1

Explanation 

Simple trick behind this formula to get the number of words in a cell is we substract the length string without spaces from the length of string with normal spaces and add 1.

In the example shown, the formula in C3 is:

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

How this formula works

TRIM function returns a string with leading spaces, extra spaces and trailing spaces removed. In the example, I give 2 Leading spaces, 3 extra space and 2 trailing space and get removed by TRIM

=TRIM(A1)

To get the length of the string with normal spaces, we combine the LEN and TRIM function.

=LEN(TRIM(A1))

The SUBSTITUTE function replaces existing text with new text in a text string. 



We use the SUBSTITUTE function to get the string without spaces.

=SUBSTITUTE(A1," ","")

To get the length of the string without spaces, we combine the LEN and SUBSTITUTE function.

=LEN(SUBSTITUTE(A1," ",""))







Now comes the simple trick. 



To get the number of words, we subtract the length of the string without spaces (10) from the length of the string with normal spaces (12) and add 1.

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

Simple, isn't it!