Find Text and get result

Hi All,

I need help in getting a excel formula to find words from a list of keywords which are in a free text cell. I want this formula to return the value as the keyword found in the text instead of just True or False.

in Column A i have Free Text, and in Col F I have keywords, In Col B and C I want text results upto two levels. for Example If in col A I have Text as "I work for a company" then in Col B it should show "Work" and in Col C it should show "Company"

(Sorry I am not able to upload a test sheet, below is the data I have in sheet)

Column A
Free Text
My name is Rob
I work for a company
I work as an advisor


Column B
Result 1
Name
Work
Work


Column C
Result 2
NA
Company
Advisor


Column F
List of Keywords
Name
Work
Company
Advisor

If you put the actual keywords in B1:E1, then you can use this formula in B2:

=IF($A2="","",IF(ISNUMBER(SEARCH(B$1,$A2)),B$1,""))

then copy across and down.

Hope this helps.

Pete

Hi Pete,

Thanks for your reply, but my Keywords has to be listed vertically and I cannot put it horizontally as I might have 100s of keywords. Is there a way out..?

Hi Pete,

Thanks for your reply, but my Keywords has to be listed vertically and I cannot put it horizontally as I might have 100s of keywords. Is there a way out..?