Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Newbie: Counting the number of words in a column

1 view
Skip to first unread message

CF

unread,
May 4, 2004, 2:32:18 PM5/4/04
to
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.

unread,
May 4, 2004, 2:55:49 PM5/4/04
to
As a noob, you should first try searching google groups for such a thing..

http://tinyurl.com/34jqy

"CF" <cfea...@bellnet.ca> wrote in message
news:xmRlc.1007$dn2....@news20.bellglobal.com...

Frank Kabel

unread,
May 4, 2004, 3:10:33 PM5/4/04
to
Hi
try
=SUMPRODUCT((LEN(A1:A5000)-LEN(SUBSTITUTE(A1:A5000,"
","")+1)*(A1:A5000<>""))

--
Regards
Frank Kabel
Frankfurt, Germany

Harlan Grove

unread,
May 4, 2004, 3:31:35 PM5/4/04
to
"Dave R." wrote...

>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.

--
To top-post is human, to bottom-post and snip is sublime.

Harlan Grove

unread,
May 4, 2004, 3:35:24 PM5/4/04
to
"Frank Kabel" wrote...

>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

unread,
May 4, 2004, 3:46:10 PM5/4/04
to
Thanks Frank...


"Frank Kabel" <frank...@freenet.de> wrote in message
news:OP90stgM...@TK2MSFTNGP09.phx.gbl...

Dave R.

unread,
May 4, 2004, 3:57:12 PM5/4/04
to
:)


"CF" <cfea...@bellnet.ca> wrote in message

news:ZrSlc.1010$dn2....@news20.bellglobal.com...

Frank Kabel

unread,
May 4, 2004, 3:57:29 PM5/4/04
to
Harlan Grove wrote:
> "Frank Kabel" wrote...
>> 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.

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

0 new messages