Formula for finding a word phrase in an entire column/row

[faizzsheikh]

I have a range of cells with too many words in rows and columns. I want to find how many times a particular "Word Phrase" is repeated in the entire range of cells.


For example:

Cell ranging from A1 to A200 have an average of 25 words in each cell.

Now, I have a list of phrase eg. 'Awesome In Excel', 'I recomment Chandoo', 'Chandoo Forum', 'Learn Excel' and so on. I want to find the number of times each of this word is been used from the entire range i.e A1 to A200.


Thanks in advance for your answers!


Regards,

Faiz

 

[Faseeh]

Hi Faiz,


Welcome to the fourm!!


with your data like below in col A:

[pre]

Awesome in Excel
I Recommend Chandoo
Chandoo Forum
Learn Excel at Chandoo[/pre]

You can use: 


=COUNTIF($A$1:$A$4,"*"&"Chandoo"&"*")

Regards,

 

[faizzsheikh]

Hi Faseeh,


Thanks a lot for the help.


In this formula: =COUNTIF($A$1:$A$4,"*"&"Chandoo"&"*")


Instead of typing 'Chandoo' in the formula, can I give the cell reference of this word? Because, since there are too many words that I have, I cannot type each of the word in the formula. I have more than 2000 words to find.


==============


Also, this formula works perfect for the range of cells. But when I try to use it for a single cell it doesn't work.


Eg. The cell A1 have this statement:

"Chandoo is good to learn excel, Chandoo can help you with excel, Chandoo is a great source to learn excel"


It doesn't work when I use: =COUNTIF($A$1,"*"&"Chandoo"&"*")


It always returns me the value '1' instead of '3'.


Thank You,

Faiz

 

[Faseeh]

Hi,


Place this formula in a column and drag down and sum the resulting values, your data is in col A;

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

Off course you can replace "Chandoo" by cell reference (eg. A10 or else)


Regards,

 

[Faseeh]

Hi,


You can use this as well:

=IFERROR((SUM(LEN($B$8:$B$13))-SUM(LEN(SUBSTITUTE($B$8:$B$13,$B$3,""))))/LEN($B$3),"-")

Your data is in B8:B13 and B3 contains the criteria, press Ctrl+Shift+Enter to execute, there is no need for helper column.


Regards,