Find an exact word within a text string

Hello guys,

I come to this forum in order to make a question related to excel and vba. The thing is that I have a column with approximatly 150 keywords (Column A), and another column with 500 sentences (Column B), what I would like to obtain in the column C, is the keyword from the column A which is within the sentece in the column B. I mean, checking for example de Keyword in A1, with all the sentences in column B, and if this word is included in a sentece, then writting that word in the column C. When I say "included", I want to say that the word must be excatly the same. Example:

A B C

green The green car green
blue The greenth house -
red The red trousers red
yellow He has a yellower wallet -


It would be really appreciate an answer as soon as possible

Thanks a lot and regards!

Thanks a lot for your quick answers!! but neither of them worked. I have attached a sample of what I have, and what I want to achieve. Maybe the only solution is to create a function which takes every keyword from the column A and compare it with every sentence of the column B, and then it gives the word found in the column C. As a warning!, I would like to obtain in the column C, exactly the same Keyword. For example in the column b can be included the word "windows" which is different from the word "window" which is a keyword, therefore it is not the same and it should not appear in the column C. (Capital and small letters must be taken into account, since the words can be presented in both formats and are the same)

Here it is the file...

Thanks a lot Andy Pope!!! It works perfectly!! but the problem comes when the text string, in which I want to see if the keyword is included, is too long and then it appears a message saying (Value!). The thing is, when I delete "chart (32)" from the formula, it works. Why is it happening that? and what is exactly the meaning of "chart" in the formula? Should I have to remove from it or keep it?.

Thanks a lot again!! your help has been really useful!!!

According to you, I understand if the sentence has more than 32 spaces, it is not going to be taking account no? or how does it work?

Hi friend
Try this formula according to my knowledge after used and then tell your valuable feedback.

Formula:

Please Login or Register  to view this content.

Yes, it is true, I have attached an example. As you can see in this file, keywords like: baterry, drive or webcam are not taken into account. I don't know if it is a problem with the function, or it is about the word "chart" within the function; but the thing is those words don't appear in the column C as words which are included in the sentences of the column B, and appear in the column A as Keywords. What can it be happening in this case? could you have a look and propose the "perfect solution"?

Thanks a lot for your help!

Great!!!! it works awesome!!! now I have an another challenge for you! If a part from the commas, I use "/" or "-" or "." to separate the different words aswell. How would I have to modify the function? Would I have to introduce three new loops?. Thanks a million!!!

This is what I have tried in order to remove ".",",","/" and "-". The problem comes when I obtain the results in the column C, because the function gives me back all the words that I want, but some of them repeated. What can I do to optimise this function? is there a better solution?. Thanks a lot for your help!!





Function udfKEYWORDS(LookIn As Variant, UseWords As Variant, Optional Delimiter As Variant = " ") As Variant

Dim strTemp As String
Dim vntWord As Variant
Dim strWord As String
Dim vntItem As Variant
Dim vntLookin As Variant


strTemp = ""
vntLookin = Replace(LookIn, ",", " ")

For Each vntItem In Split(vntLookin, " ")
If Len(Trim(vntItem)) > 0 Then
For Each vntWord In UseWords
strWord = Trim(vntWord)
If Len(strWord) > 0 Then
If StrComp(strWord, Trim(vntItem), vbTextCompare) = 0 Then


If InStr(1, strTemp, strWord, vbTextCompare) > 1 Then
' already used
Else
strTemp = strTemp & vntItem & Delimiter

End If
Exit For
End If
End If
Next
End If
Next
vntLookin = Replace(LookIn, "-", " ")
For Each vntItem In Split(vntLookin, " ")
If Len(Trim(vntItem)) > 0 Then
For Each vntWord In UseWords
strWord = Trim(vntWord)
If Len(strWord) > 0 Then
If StrComp(strWord, Trim(vntItem), vbTextCompare) = 0 Then


If InStr(1, strTemp, strWord, vbTextCompare) > 1 Then
' already used
Else
strTemp = strTemp & vntItem & Delimiter


End If
Exit For
End If
End If
Next
End If
Next
vntLookin = Replace(LookIn, "/", " ")
For Each vntItem In Split(vntLookin, " ")
If Len(Trim(vntItem)) > 0 Then
For Each vntWord In UseWords
strWord = Trim(vntWord)
If Len(strWord) > 0 Then
If StrComp(strWord, Trim(vntItem), vbTextCompare) = 0 Then


If InStr(1, strTemp, strWord, vbTextCompare) > 1 Then
' already used
Else
strTemp = strTemp & vntItem & Delimiter


End If
Exit For
End If
End If
Next
End If
Next
vntLookin = Replace(LookIn, ".", " ")
For Each vntItem In Split(vntLookin, " ")
If Len(Trim(vntItem)) > 0 Then
For Each vntWord In UseWords
strWord = Trim(vntWord)
If Len(strWord) > 0 Then
If StrComp(strWord, Trim(vntItem), vbTextCompare) = 0 Then


If InStr(1, strTemp, strWord, vbTextCompare) > 1 Then
' already used
Else
strTemp = strTemp & vntItem & Delimiter


End If
Exit For
End If
End If
Next
End If
Next
If Len(strTemp) > 0 Then
strTemp = Trim(Left(strTemp, Len(strTemp) - 1))
Else
strTemp = "-"
End If

udfKEYWORDS = strTemp

End Function

Andy pope, I do not have enough words to describe you!!! unbelievable!!! it works perfect!!! you are the master!!! by the way, if I wanted to add another exception like ";", would I have to add it in this way "=udfKEYWORDS(B2,$A$2:$A$13," ,.-/;",CHAR(32))"? Thanks a million!!!