NThe number of cells that contains a particular text

[faizzsheikh]

Need Urgent Help! - The number of cells that contains a particular text

I have a range of cells containing content and another range of cells containing just words.

Eg:

Cell Range A:A1000 contains paragraphs/content

Cell Range B:B500 contain words/phrase. Each word occurs multiple times under column A. For instance the word 'great work' appears in multiple cells under column A, likewise there are hundreds of such words I have to find the cell references for from column A.


Please guide me how to make this possible.


It will be a great help!


Thank You,
Faiz

[Sam Capricci]

you could set up a word reference list then do a countif on it, or set up a filter and then filter on contents containing the word string you want.

[Sam Capricci]

A countif might look something like...
=countif(A2:A1000,"*great job*") or a reference to the list you set up.

[faizzsheikh]

Hi Sambo kid

thanks for your reply. Actually I don't want the number of times the word appears under the cell range.

I actually need the cell reference in result. Like all the cell references that contains the word "great job"

[Sam Capricci]

That's an interesting twist. I'll have to think about it.

[Sam Capricci]

Hmmm, the best I can come up with is to use a filter then filter by text and contains.
in conjunction with a countif formula you'll at least know if you have them all once you filter on it.
Other than that I tried different formulas with find and address but w/no success.
Hopefully someone w/better skills than mine will drop by and give you a better solution.

[XOR LX]

Hi,

I think this is a particularly apt case for a workbook containing some examples and desired results. Without, theoretical solutions will always be problematic, I'm afraid.

Obviously remove/replace any confidential/sensitive information in there, though be sure to give enough examples so that each different type of string in your actual data is comprehensively accounted for.

Regards

[sktneer]

Suppose you have text content in the range A2:A1000 and one of the one of the word/phrase from col. B is "Great Work" (As per your example), then use the below formula to return the cell reference of the the cells from the col. A which contain that word/phrase.
It is an array formula, so confirm it with Ctrl+Shift+Enter and drag it down.

Please Login or Register  to view this content.

Is this what you want?