Lookup words contained in a cell

**will.00** · 07-30-2009, 07:38 AM

Hi

I have a series of jumbled data (column A). Within the data set are certain key words highlighted (ie fruit names) that I want to strip out in a separate column (column B). Is there a lookup function that can lookup characters contained in a cell.

Thanks in advance

Long Description [B]
031660UAPPLEBRCIPI0
BRCIPI0ORANGEUBRC
JBUPEAR00031653UBRC
GFFUBORANGE0059716
OUBORANGE572UB
BKUBRCIPEAR555UBR
AGY5UBAPPLE001924
8YUBRAPPLE3413U
8AIYFPEARI00033405U
I3IUBORANGE33408U
HEFUBPEAR0197510UB

**DonkeyOte** · 07-30-2009, 07:44 AM

one way...

list words of interest in a range, say E2:E4 contain APPLE,PEAR,ORANGE

B2: =INDEX($E$2:$E$4,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($E$2:$E$4,$A2)),0),0))
copied down to B12