Searching partial string

  • I have two tables :


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    [TABLE="width: 155"]

    [tr]


    [TD="width: 155"]PN

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 107"]

    [tr]


    [TD="width: 107"]Model

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 155"]

    [tr]


    [TD="width: 155"]SN74LVC1G08DCKR

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    [TABLE="width: 155"]

    [tr]


    [TD="width: 155"]TLV3492AIDCNT

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    [TABLE="width: 155"]

    [tr]


    [TD="width: 155"]TPS2031DRG4

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [/tr]


    [/TABLE]


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]Code

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]Model

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]DCK

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]Golf

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]DRG

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]Bora

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]DCN

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    [TABLE="width: 64"]

    [tr]


    [TD="width: 64"]Polo

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [/TABLE]


    The purpose is to get for each PN relevant Model, by partial string.

  • Re: Searching partial string


    Three rows given only for a reference. The original file is huge with a thousand of rows.
    I would prefer to have a function , equivalent to VlookUp. So finally I will get :
    [TABLE="width: 233"]

    [tr]


    [td]


    PN

    [/td]


    [td]


    Model

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    SN74LVC1G08DCKR

    [/td]


    [td]

    Golf

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td]

    TLV3492AIDCNT

    [/td]


    [td]

    Polo

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    TPS2031DRG4

    [/td]


    [td]

    Bora

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

  • Re: Searching partial string


    You still have not described the logic. Looking at your very limited sample it could be assumed the 'partial string' you want is the first 3 of the last 4 characters to determine the model. Maybe it is, maybe it's not. No one except you knows.


    If that does happen to be what you want I would use a simple VLOOKUP using those 3 characters to return the model from a list stored elsewhere.

  • Re: Searching partial string


    I can understand that. But, it would be a lot simpler if you could say that the string you want to search for appears in a position where it can be determined, I find it usually does in model/serial numbers. Hard to explain, but your first post always used the first 3 of the last 4 characters - If that were true in all cases you could use a simple VLOOKUP

  • Re: Searching partial string


    Over x,000's of rows, it's not improbable that 'DCN', as 1 example, will appear in the the string but not as a model designation. What do you do then?

  • Re: Searching partial string


    Perhaps try something like this in B2:


    [COLOR="#0000FF"]=IFERROR(LOOKUP(9.99999999E+307,SEARCH(Sheet2!$A$2:$A$100,A2),Sheet2!$B$2:$B$100),"not found")[/COLOR]


    where Sheet2!A2:B100 contains the lookup table and A2 contains first PN value.


    Note: Do not include blank rows in your lookup range. If the range is a growing range, then use a Named Range to define it.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!