Find, List and Count All Words within a Text String

**TheDrMrsSmith** · 02-26-2014, 08:57 PM

Hi Everyone,

Please help, as I cannot find a clear cut answer. Thus, I have uploaded my spreadsheet.

I have a column of text strings on Sheet1, Column A, which I need to check for the presence of keywords listed on Sheet2, Column A

So if any word from the keyword list on Sheet2, Column A is found in, say, cell A2 of Sheet1, the cell to its right (B1) should have a formula to display the count of keywords found in A2. I also would like to see each keyword identified either through a highlight or a list. I need the formula to NOT be case sensitive and the match does not have to be for whole words).

I have also attached an example of the spreadsheet.

Thanks!

**p24leclerc** · 02-28-2014, 10:29 PM

This formula will at least give you the number of keywords in the text string.

Please Login or Register  to view this content.

Please note that this formula is an ARRAY formula, you must hit the keys CTRL+SHIFT+ENTER to exit the formula to make it work.
In order to highlight those specific words, you'll probably need VBA macro.

**FDibbins** · 02-28-2014, 11:07 PM

If your text is in A1, use this to break it apart, copied across...
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),LEN($A1)*(COLUMN()-2)+1,LEN($A1)))

Note, this is not my formula, a really smart member cooked this up and I saved it for just such a request