How to find variable length/position text string in cell

[hizzle]

Can't quite figure this out.

I have a list of part numbers (about 20 characters, containing numbers, letters, symbols and spaces) and what I am trying to do is look for certain "codes" within each part number and put them in a separate column. The code does not always appear at the same position in the part number, nor is it always the same length.

For example, I am trying to take the part numbers
213241KRT : purplewidget
2355434634QCK yellowpencil
43GRVRD : bluephone

And for each part number I want to look and see if the cell contains the code "KRT" or "QCKT" or "GRVRD", and if so then put whichever code is contained in the part number in the column next to it.
I'm guessing this requires some kind of macro, but I haven't been able to find anything that quite works. Any ideas would be very helpful. Thank you!

 

[hizzle]

Ooops - sorry about that - I should have been providing more info.

The words "purplewidget" etc are actually part of the part number. I should have probably just put a string of characters instead to make it a more generic example.

Thanks again - these examples have been a huge help - and answered so quickly...I really appreciate it everyone!

 

[Krishnakumar]

Another option..

=LEFT(SUBSTITUTE(A2,LOOKUP(9.9999999E+307,--MID(A2,1,ROW($1:$1024))),""),FIND(" ",SUBSTITUTE(A2,LOOKUP(9.9999999E+307,--MID(A2,1,ROW($1:$1024))),""))-1)

HTH

 

[hizzle]

barry-
I just tried our your solution example and the code returns "0" when it is searcing for BARRY. I assume something simple I am doing wrong...
Thx

 

[barry houdini]

The formula is case-sensitive so if you have BARRY uppercase in the codelist it will only give a match if BARRY uppercase is found, if you don't want it to work that way then replace "FIND" with "SEARCH" in the formula(s)

SEARCH is not case-sensitive

edit: or....have you removed the text "codelist"? that will affect the formula...