Count number of words in a cell

Count number of words in a cell :-

Here in below example we have list of name, we need to count each word and skip blank, if any.

Formula:
=LEN(TRIM(A3))-(LEN(TRIM(A3))-LEN(SUBSTITUTE(TRIM(A3)," ","")))

Explanation:

• TRIM function used to remove all spaces from text except for single spaces between words.
• (LEN(TRIM(A3))-LEN(SUBSTITUTE(TRIM(A3)," ",""))), function used to count single spaces between words. As SUBSTITUTE function here we used to delete spaces between words and LEN function to count the trim text with and without spaces.
• LEN(TRIM(A3))-(LEN(TRIM(A3))-LEN(SUBSTITUTE(TRIM(A3)," ",""))) , subtract the length of trim text with no space trim text.

Hence, we get our desired result i.e total number of words in a cell.

Thank You !!