Clean up text
Count the words in a cell
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1How it works in Excel
- TRIM removes extra spaces.
- Taking out the spaces and comparing lengths counts the spaces.
- There is always one more word than spaces.
- An empty cell gives 1, so add an IF if that matters.
The idea in general
Words have gaps between them, so you count the gaps.
What I assumed
- The text is in A2.
Check the result on your own data before relying on it.