+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    02-05-2020
    Location
    australia
    MS-Off Ver
    Office 365 Business
    Posts
    4

    Lightbulb 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.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,203

    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. #3
    Registered User
    Join Date
    02-05-2020
    Location
    australia
    MS-Off Ver
    Office 365 Business
    Posts
    4

    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. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,203

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

    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

  5. #5
    Registered User
    Join Date
    02-05-2020
    Location
    australia
    MS-Off Ver
    Office 365 Business
    Posts
    4

    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-ADxxx || Blah-IC-900 || IC-900
    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.
    Last edited by Roopansh; 02-05-2020 at 10:15 PM.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,203

    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. #7
    Registered User
    Join Date
    02-05-2020
    Location
    australia
    MS-Off Ver
    Office 365 Business
    Posts
    4

    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. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,203

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Script to Search for a specific String then return cell values above that String-4
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2015, 01:28 AM
  2. [SOLVED] Script to Search for a specific String then return cell values above that String-3
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2015, 01:50 PM
  3. [SOLVED] Script to Search for a specific String then return cell values above that String-2
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2015, 01:00 PM
  4. [SOLVED] Script to Search for a specific String then return cell values above that String
    By owensjb2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2015, 11:28 AM
  5. Search column for string and return every row with that string in new sheet
    By myers601 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2012, 06:02 PM
  6. Replies: 3
    Last Post: 07-27-2011, 02:29 PM
  7. Replies: 2
    Last Post: 02-26-2008, 03:22 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1