FInding a string within a string ?

[JumboCactuar]

Hi
this is probably basic stuff but I've never done this before

I have a code table:

RED	Red
BLA	Black
BLU	Blue
YEL	Yellow
PIN	Pink

etc....

and I have long strings like
002147YELXR3
00732933BLA22
etc...

trying to output in next column to string the name of the code like above would be
Yellow
Black

if the color codes we're in fixed position I could do this easily but the codes can be in ANY part of the string.


Best way to do this ?

thanks

 

[JumboCactuar]

If Yes just replace the search

(SEARCH(B2:B200000,I2))

with this

SEARCH(IF(($B$2:$B$20000<>""),($B$2:$B$200000),REPT("Z",900)),I2)

thanks this works now

 

[CA_Punit]

Glad it worked.