Counting words in cell or range of cells.

[tygon4]

I am trying to come up with a formula that will allow me to count the number of words in a cell or range of cells e.g.
A1 has text "AB CD"
A2 has text "AB CD EF"
The result I desire is 5. I have tried using countif but havent been able to make it work for me. Do I need a custom function for this? If so any suggestions would be be appreciated.

 

[DonkeyOte]

Well as long as you don't use a " " after just one word - e.g. A1 = AB not A1 = AB_ (_ meaning space)

You can count the spaces - so if A1 = AB CD then formula would be =COUNT(" ",A1)+1

 

[DonkeyOte]

meant COUNTIF

 

[Aladin Akyurek]

=SUMPRODUCT((LEN(TRIM(A1:A10))-LEN(SUBSTITUTE(TRIM(A1:A10)," ",""))))+COUNTA(A1:A10)

where A1:A10 houses the target text values.

 

[tygon4]

Thanks Aladin that does the trick perfect.

 

[sen_edp]

Hello Everyone

For your info , Colo created a nice user defined function , that counts words.
take a look, if you want at:

http://www.mrexcel.com/board/viewtopic.php?topic=11109&forum=2

hth