counting specific words

[jim]

is it possible to display the number of times a specifc word appears in a cell or worksheet??

 

[Frank Kabel]

Hi Jim
If A1 is the cell which contains 0 - n of your words and A2 stores this
word try
=(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)

to get the number the word/substring appears in cell A1

 

[Guest]

This did not work...do have any suggestions?

-----Original Message-----
Hi Jim
If A1 is the cell which contains 0 - n of your words and A2 stores this
word try
=(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)

to get the number the word/substring appears in cell A1


--
Regards
Frank Kabel
Frankfurt, Germany


.

 

[Frank Kabel]

Hi
what was the error you recevied as this should do?

 

[darkangelstorm]

I guess you could set up a countif function to count the numbers o
words or numbers.

E.g COUNTIF(D2:N2,"5"))

so between D2 and N2, thats what it looks for . im searching for th
value "5" which you could change to letter or w/

 

[Guest]

I figured it out. I re-oriented the formula to line up by
column rather than row, so its now:

=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)

It works fine. (it's just not 2 in the morning now)
Thanks!Wynn

 

[Frank Kabel]

Hi
good to hear it works for you