Searching for multiple words in a cell

Hello,

I'm trying to search for a list of words within a cell that contains text and have those words displayed in the column adjacent to it.

For example:

A1: The cat in the hat.
A2: The dog in the hat.
A3: The walrus in the hat.
A4: The cat on the mat.
A5: The cat and dog on the mat.

B1:B5 contains the search terms I'm looking for:
B1: Cat
B2: Dog
B3: Walrus
B4: Hat
B5: Mat

I would like C1:C5 to show whether the search terms in B1:B5 are found in A1:A5, and then display those results (ie C1 should show 'cat and hat' and C2 should show 'dog and hat' and C5 should show 'cat, dog and mat').

Currently I'm using =IF(COUNT(SEARCH({"Cat","Dog"},A1)),"Cat","") but this formula isn't particularly useful for my purposes since it is TRUE/FALSE rather than returning the actual search term.

I would be very grateful for any help - thank you so much in advance!

Insert this code into your VB editor (Alt +F11, Insert|Module)

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then try formula in C1:

=SUBSTITUTE(TRIM(ACONCAT(IF(ISNUMBER(MATCH("*"&$B$1:$B$5&"*",A1,0)),$B$1:$B$5,"")," "))," ",", ")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

Thank you so much for your very helpful reply! The vba worked perfectly for my example.

I tried replicating this for a more complicated text - the US Constitution as an example but the results aren't so consistent. There's also a problem with the "0" and commas...since comma separators aren't necessary, I'm wondering if it would make the formula simpler if they were left out?

I was actually quite skeptical before about whether excel had the power to do this type of search but your macro gave me a lot of confidence. If you have any further suggestions on this, I really would appreciate it!

(I've attached my example file to this post)

Edit: can't attach the file for some reason...

Try condensing the file down or zipping it.

Try:

=SUBSTITUTE(SUBSTITUTE(TRIM(ACONCAT(IF(ISNUMBER(FIND($C$2:$C$17,B3)),SUBSTITUTE($C$2:$C$17," ","^^"),"")," "))," ",", "),"^^"," ")

confirmed with CTRL+SHIFT+ENTER not just ENTER.

This is case sensitive so if the string in column B has "congress" it will not pick up as a match to "Congress" in column C.

I fixed it up so that you can still have comma delimiters, but it won't affect multi word entries like "Unites States".

In some of your comments, you don't include "Congress" as being an expected match, like in D2.... but "Congress" does exist in B2.

You also say that D15 should be blank, but I find that some of the words in column C do, in fact exist in B15 (and same with B16/D16 in case you are off by 1 row in your references in the comments).

Also, I did copy the formula down to past 3000 rows and got no errors.... you just have to wait for the recalculating to finish as this is an array formula and can be slow when copied down thousands of rows.

If I extend the keywords list to include blanks, I don't get the 0's you say you are getting.

Also, do not use whole columns are extra large ranges as this will slow down the recalculations considerably.

You can create a dynamic range that "grows" with the addition or removal of keywords in the list.

To do that go to Insert|Name|Define and enter the word Keywords in the names in this workbook field... and enter formula:

=OFFSET(Sheet1!$C$1,1,0,COUNTA(Sheet1!$C:$C)-1,1)

in the source field.

Then change the formula in D2 to:

=SUBSTITUTE(SUBSTITUTE(TRIM(ACONCAT(IF(ISNUMBER(FIND(Keywords,B3)),SUBSTITUTE(Keywords," ","^^"),"")," "))," ",", "),"^^"," ")

Do you mean you don't want it to pick up Power when the sentence contains Powers?

If so, try:

=SUBSTITUTE(SUBSTITUTE(TRIM(ACONCAT(IF(ISNUMBER(FIND(" "&KeyWords&" "," "&SUBSTITUTE(SUBSTITUTE(B2,".",""),",","")&" ")),SUBSTITUTE(KeyWords," ","^^"),"")," "))," ",", "),"^^"," ")

CSE confirmed

But note, that if you have words that have a symbol next to them like for example in this sentence: I am the best (President) of all time. Then it would not pick up President because it has a bracket before and/or after... I have used SUBSTITUTE functions to replace periods and commas, you can add more for the brackets, but there could be other symbols, like semi colons or colons, or exclamation points, or question marks, etc....

If you noticed, I added a " " before and after the KeyWords named range. This forces the find to look for words that contain one of the keywords, but with a space before or after, this indicating to search for the exact word and not the word within a word like Presidential.... I accounted for the keyword potentially landing at the beginning or ending of a sentence by adding " " before and after the reference to B2... so it automatically adds a space at start and end of each sentence... then I incorporated a work around to replace commas and periods with nulls so that words next to those punctuations are captured... but if you have too many possible punctiations, it becomes more involved... in XL2003 you can only have up to 7 nested functions, so SUBSTITUTE for each possible symbol won't work...

Maybe an easy alternative would be to copy column B into a new column and then use Edit|Replace from the Excel Menu to replace each possible symbol, one by one, then the formula should work (and you can remove the SUBSTITUTE(SUBSTITUTE(B2,".",""),",","") part and replace with just B2... well, in fact, you would need to change B2 to the new column reference... but you get the idea.