Newbie: Counting the number of words in a column

  • Thread starter Thread starter CF
  • Start date Start date
C

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
 
Hi
try
=SUMPRODUCT((LEN(A1:A5000)-LEN(SUBSTITUTE(A1:A5000,"
","")+1)*(A1:A5000<>""))
 
As a noob, you should first try searching google groups for such a thing..

http://tinyurl.com/34jqy
Click to expand...
...

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.
 
try
=SUMPRODUCT((LEN(A1:A5000)-LEN(SUBSTITUTE(A1:A5000,"
","")+1)*(A1:A5000<>""))
Click to expand...
...

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.
 
Harlan said:
...
..

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.
Click to expand...

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