Find Word in text string - reproduce it in another cell

[pinkgold]

I have been using =SUMPRODUCT(--(ISNUMBER(SEARCH(Sheet2!A1:A81,Sheet3!A1))))>0 to search a list of words on sheet2 and search through a text string to return a truth value dependent on whether or not the word was found in the text string. What can I add so that, once this word is found, it is then copied (on its own) to a seperate cell.

Any help on this would be greatly apprciated.

Thanks

Pinkgold

[tigeravatar]

Pinkgold,

Give this a try:

Please Login or Register  to view this content.

[pinkgold]

Thanks for the help, I've tried that, and its returning nothing at all. I must admit, its beyond me tbh. The text string is in A1 Sheet 2. Any ideas?

[tigeravatar]

In your original formula, you have the text string in Sheet3!A1 and the text to search for is listed in Sheet2!A1:A81, so I was going off of that information.

Change Sheet2!$A$1:$A$81 (both instances) to the sheet and cell range containing the text to look for
Change Sheet3!A1 to be Sheet2!A1 since that is where you've stated the text string is.

[pinkgold]

Sorry I misquoted there .... it is indeed located at A1 of Sheet 3. Apologise. Still nothing returned ..

[tigeravatar]

In that case it's probably time for an example workbook.

[pinkgold]

Here you go. Thanks again for the help!


Example Workbook.xlsx

[tigeravatar]

Please Login or Register  to view this content.

[pinkgold]

Wow, sorry! Thank you so much!!!

[tigeravatar]

You're very welcome