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

1. ## 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.

2. ## Re: Search for a string of code from a column in another string and return the code

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

3. ## Re: Search for a string of code from a column in another string and return the code

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.

4. ## Re: Search for a string of code from a column in another string and return the code

=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

5. ## Re: Search for a string of code from a column in another string and return the code

Nope this is just adding the product code in the last of the extracted part.
So, right now let's just say there are 3 following columns: The ideal scenario will look like this:

PC(Product Code) || String || EC(Extracted Code)
IC-900 || Some text || No code in this string

Currently, the solution from your formula is generating the answer in front of the PC column (code in PC suppose IC-ADxxx, if it's there somewhere in the "String" column, it's returning the product code in front of the PC row.)
In the ideal solution, each string has to be checked, and if there is any code present in that string that matches (from the PC column), return that code in front of that string. Like above, since the string Blah-IC-900 contained a product code, that code was returned in front of that string itself.

Thanks a lot, man for providing your valuable time in looking into this problem with me.

Let me know if something is not clear please.

6. ## Re: Search for a string of code from a column in another string and return the code

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

7. ## Re: Search for a string of code from a column in another string and return the code

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.

8. ## Re: Search for a string of code from a column in another string and return the code

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

There are currently 1 users browsing this thread. (0 members and 1 guests)