Counting words in cells

[Guest]

Is there a way of counting the number of words in a cell, basically an Excel version of the NUMWORD field in Word
If not, if there a clever trick I can use to end up with the same result?

 

[Frank Kabel]

Hi
try the following to count word
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

 

[Chip Pearson]

Try the following formula:

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


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Is there a way of counting the number of words in a cell, basically an

Excel version of the NUMWORD field in Word?

 

[Guest]

Brilliant! Both work equally well (although Frank's returns 1 for empty cells - not a problem for me though)
Thanks very much for your quick replies
Cheers