software

if i have a paragraph in one cell
and want to count the number of words in the paragraph, what do i do

say the cell has the following quot;I went to the school today. School was
fun.quot; all in a1,

how do i count the number of times school is in a1?--
kckar
------------------------------------------------------------------------
kckar's Profile: www.excelforum.com/member.php...oamp;userid=25322
View this thread: www.excelforum.com/showthread...hreadid=512954
Try

=(LEN(A1)-LEN(SUBSTITUTE(A1,quot;schoolquot;,quot;quot;)))/6

The 6 is the length of the word for which you're searching, if you put
your search word in a cell, say C2

=(LEN(A1)-LEN(SUBSTITUTE(A1,C2,quot;quot;)))/LEN(C2)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=512954
wouldnt that count all 2 letter words such as quot;isquot; and quot;itquot; instead of
just counting quot;itquot;

or all words that have seven letters instead of just counting how many
times school shows up--
kckar
------------------------------------------------------------------------
kckar's Profile: www.excelforum.com/member.php...oamp;userid=25322
View this thread: www.excelforum.com/showthread...hreadid=512954No because it specifically looks for the text 'school', not just any old 6
character string. It just divides by 6 as the first part of the formula
counts the number of characters taken up by the word school (12 if there are
2), and so needs to divide by the length of the search.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;kckarquot; gt; wrote in
message ...
gt;
gt; wouldnt that count all 2 letter words such as quot;isquot; and quot;itquot; instead of
gt; just counting quot;itquot;
gt;
gt; or all words that have seven letters instead of just counting how many
gt; times school shows up
gt;
gt;
gt; --
gt; kckar
gt; ------------------------------------------------------------------------
gt; kckar's Profile:
www.excelforum.com/member.php...oamp;userid=25322
gt; View this thread: www.excelforum.com/showthread...hreadid=512954
gt;
You might set up a user defined function like:

_ _ _ _ _ _ _ _ _ _

Function CountWdInstances(rngA As Range, strSeek As String) As Integer
arrayWords = Split(LCase(rngA.Text))
arrFilteredList = Filter(arrayWords, strSeek)
CountWdInstances = UBound(arrFilteredList) 1
End Function
_ _ _ _ _ _ _ _ _ _Now, if you use
=CountWdInstances(A1, quot;schoolquot;)
You should get the number of times quot;schoolquot; appears in the range. If you
want it to be case sensitive, drop the LCase inside the Split function.

Steve Yandlquot;kckarquot; gt; wrote in
message ...
gt;
gt; if i have a paragraph in one cell
gt; and want to count the number of words in the paragraph, what do i do
gt;
gt; say the cell has the following quot;I went to the school today. School was
gt; fun.quot; all in a1,
gt;
gt; how do i count the number of times school is in a1?
gt;
gt;
gt; --
gt; kckar
gt; ------------------------------------------------------------------------
gt; kckar's Profile:
gt; www.excelforum.com/member.php...oamp;userid=25322
gt; View this thread: www.excelforum.com/showthread...hreadid=512954
gt;