Search for a string of code from a column in another string and return the code

Hi, I have a problem to solve which is explained in the following 3 steps:


1. One column is there in which there are over 20,000 text strings which contain random data. Strings can be like: "$300 and above Weather Stations" OR like "Kestrel 5500 Weather Meter - IC-0855".
2. Another column contains 10,000 unique product codes.Codes can be like: "0810-0004
" or "IC-0855"
3. The task is to search every string (in step 1), if there is any of the product codes present in that string, return that product code in front of that respective string. For example from the first two steps, I can see that the string "Kestrel 5500 Weather Meter - IC-0855" contains a product code (IC-0855) mentioned in the column containing product codes, so I want to return that product code in front of this string,

I am attaching the sample data file if someone can help me with this. I thought VLOOKUP could help in this, but I couldn't find out a way around it, too.
Thanks in advance.

Try this formula (I put it in E2, for comparison):

=IF(COUNTIF($B$2:$B$12,"*"&A2&"*"),A2 &": "&SUBSTITUTE(INDEX($B$2:$B$12,MATCH("*"&A2&"*",$B$2:$B$12,0))," - "&A2,""),"")

Copy this down as required. This removes the code from the right hand end of the string, as well as placing it at the beginning, but you can have both if you like by removing the SUBSTITUTE term.

Hope this helps.

Pete

Hi Pete,

Thanks a lot for such a quick response.

I have checked the formula, and it's working good. But if instead of putting the extracted code, in front of the row of having the code, if we have to put the extracted code within a string in front of the string itself, what should be done.

Thanks once again.

Try this instead:

=IF(COUNTIF($B$2:$B$12,"*"&A2&"*"),A2 &": "&INDEX($B$2:$B$12,MATCH("*"&A2&"*",$B$2:$B$12,0)),"")

then copy down.

Hope this helps.

Pete

OK, try this one (again I put it in E2 for comparison):

=IF(ISNUMBER(FIND(" - ",B2)),IF(COUNTIF(A:A,RIGHT(B2,LEN(B2)-FIND(" - ", B2)-2)),RIGHT(B2,LEN(B2)-FIND(" - ", B2)-2),"Invalid code"),"No code found")

If there is nothing that looks like a code (i.e. there is no " - " in the string) then it returns "No code found"

If it finds what could be a code, it then compares it with the list in column A and if it is not there the formula returns "Invalid code", but if it is in column A then it returns the code itself.

Hope this helps.

Pete

Thank you, Pete. It works very good now. Just a small problem is more. So, for Codes which are present at the end of the string, they are getting extracted perfectly, but there are some strings in which the Code is given at the beginning, this formula is not working on those strings. I know this is happening because of the short sample size file I gave you, it didn't have any such example in which the code is not placed in the last.

Thanks again man.

Attach another example file which has examples of that type of record (and any others, e.g. if the code is in the middle).

Pete