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.

{=COUNT(IF(SEARCH('Words Positive and Negative'!$A$1:$A$2329,Transcripts!A1,1)<>0,1))}

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

Find, List and Count All Words within a Text String

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Find, List and Count All Words within a Text String

Re: Find, List and Count All Words within a Text String

Re: Find, List and Count All Words within a Text String

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find text string exist in cell from keys words

Find & Replace: How to find abbreviations or words written in capital words in text

excel search text, find non-match words from list, display nonmatch words

Count spaces between last two words in a text string

Find the last occurence of a text string in a list

Bookmarks

Bookmarks

Posting Permissions