Search for multiple text strings in a cell, return one of multiple corresponding but different string

[sparexcel]

Hi all,

This is my first post, so please do excuse me if it is in the wrong location, however I would sincerely appreciate your help.
I have a spreadsheet with 1,000 rows of data.
Each Cell in Column A has a different long text string.
I need to see which (if any) of 10 specific small text strings exist within each long text string.
Depending on which small text string is found I want to return a 3 digit code.
If no small text string is found I want to return "Not Found"
E.g.:
- Cell A2 contains "randomtext,randomtext,APPLE,randomntext"
- I want to see if Cell A2 contains any of the words APPLE, ORANGE, CARROT.
- I want to return "APP", "ORG", "CAR" or "Not Found"

Q: What is the most elegant way to accomplish this within a single formula that I could paste into each cell in Column B please?

Many thanks in advance for any time you spend looking at this - it is appreciated, this is really bugging me!

 

[shawnhet]

Assuming your data is on Sheet1 in column A - type a table on Sheet 2 that has your specific text in column A and your 3 digit code in column B.

Then next to your cells on Sheet1 paste the following formula and copy down.

=LOOKUP(2, 1/SEARCH(Sheet2!A$1:A$10,A1), Sheet2!B$1:B$10)

Cheers,

 

[Aladin Akyurek]

Assuming your data is on Sheet1 in column A - type a table on Sheet 2 that has your specific text in column A and your 3 digit code in column B.

Then next to your cells on Sheet1 paste the following formula and copy down.

=LOOKUP(2, 1/SEARCH(Sheet2!A$1:A$10,A1), Sheet2!B$1:B$10)

Cheers,

A costly division is not needed...

=LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!A$1:A$10,A1),Sheet2!B$1:B$10)