Newbie: Counting the number of words in a column

[CF]

Is it possible to count the number of words in a column with approximately
5000
records using a built-in function? Any help would be appreciated. Thanks in
advance...Carlos

 

[Dave R.]

As a noob, you should first try searching google groups for such a thing..

http://tinyurl.com/34jqy

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((LEN(A1:A5000)-LEN(SUBSTITUTE(A1:A5000,"
","")+1)*(A1:A5000<>""))

 

[Harlan Grove]

As a noob, you should first try searching google groups for such a thing..

http://tinyurl.com/34jqy

...

That'd prevent some of us from improving on past work.

=SUMPRODUCT(LEN(TRIM(Rng))-LEN(SUBSTITUTE(TRIM(Rng)," ","")))+COUNTA(Rng)

This also treats hyphenated words as one word and nonword tokens as words.
Counting words more strictly defined requires VBA if it's to be done robustly.

 

[Harlan Grove]

try
=SUMPRODUCT((LEN(A1:A5000)-LEN(SUBSTITUTE(A1:A5000,"
","")+1)*(A1:A5000<>""))

...

You should try it. It produces incorrect results if there are two or more spaces
in sequence or any trailing spaces anywhere in the range.

 

[CF]

Thanks Frank...

Hi
try
=SUMPRODUCT((LEN(A1:A5000)-LEN(SUBSTITUTE(A1:A5000,"
","")+1)*(A1:A5000<>""))

 

[Dave R.]

Thanks Frank...

 

[Frank Kabel]

...
..

You should try it. It produces incorrect results if there are two or
more spaces in sequence or any trailing spaces anywhere in the range.

Hi Harlan
should have added TRIM (as you did in your post). Thanks for the
correction
frank