count function

K

kckar

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 "I went to the school today. School was
fun." all in a1,

how do i count the number of times school is in a1?
 
D

daddylonglegs

Try

=(LEN(A1)-LEN(SUBSTITUTE(A1,"school","")))/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,"")))/LEN(C2)
 
K

kckar

wouldnt that count all 2 letter words such as "is" and "it" instead of
just counting "it"

or all words that have seven letters instead of just counting how many
times school shows up
 
B

Bob Phillips

No 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)
 
S

Steve Yandl

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, "school")
You should get the number of times "school" appears in the range. If you
want it to be case sensitive, drop the LCase inside the Split function.

Steve Yandl
 
Top