Word Count

[VegasL]

Hello,

I want to count # of words used in a query. See attachment.

Desired result is in Column D, which should count sample set of words that are in column c which is compared against column a, else put -


Thanks

[Sam Capricci]

either of these will return a 1 or 0 but that is all, if you have for example, wolf twice in the cell both will still return 1.
=IF(ISNUMBER(SEARCH(C2,$A$2)),1,0)
=COUNT(SEARCH(C2,$A$2))

[VegasL]

Hi Sam,

Yes your formula works but my example was bad. i have re-uploaded. Cell A2 is the default. desired result in cell d5 should be 2, as i have used cat two times in cell a2. I tried your formula and it returned only 1.

appreciate any help in advance

[AlKey]

If you want to get the total of the lookup words try this.

Please Login or Register  to view this content.

[VegasL]

Hi All, I'm looking for total count of words in column c by row, not the aggregate, as you have referneced.

Below is desired result:

Word
wolf 1
dog 1
dogs 1
cat 2
cats 1
jumps 0

[jtakw]

Hi,

Will there be "Line Feeds" with No Space in your Actual data just like your sample in Post #3?

[VegasL]

Yes there can be line breaks within data for example:

1-10
the wolf jumped over the cat dog and dogs
cat
cats

lions

[jtakw]

Then this will give you the separate word counts as you require.

For your uploaded sample, in D2 copied down:

Please Login or Register  to view this content.

See attached.

[Bo_Ry]

another way
=SUMPRODUCT(COUNTIF(C2,TRIM(MID(SUBSTITUTE(SUBSTITUTE(A$2,CHAR(10)," ")," ",REPT(" ",LEN(A$2))),(ROW(Z1:Z99)-1)*LEN(A$2)+1,LEN(A$2)))))

[azumi]

Another way and hope it works

=SUMPRODUCT((LEN($A$2)-LEN(SUBSTITUTE($A$2,C2,"")))/LEN(C2))

[Tommy90]

@azumi,

Your formula count - say cat- but cats too, as cat.
dogs as dog.....