Can A Vlookup W/Search Function Work On A Substring of Text?

  • I work for a large US trucking company. Below is a sample of a string of text I need to do a lookup on a dispatch from work. I work with hundreds of these trips daily. No two text strings are the same. "600357" is the truck number. This number can be either 5 or 6 digits long. "R0" is the fleet. "0200590" is the trip number. "01" is the leg or dispatch. "ELSL" is the Customer ID. The Customer ID can be between 3 to 6 characters long and may have letters only and sometimes letters combined with numbers. "SCA" is the origin and "DNV" the destination. The order of the information in this string is always the same. Truck number, fleet, trip number, dispatch number, Customer ID, origin, and destination. This order never varies. I need to use this text to cross reference or highlight information on other sheets to find data concerning customers (the bold underlined section) or other uses. I presently import as CSV file and then have to manually manipulate the data to get what I want. And then use vlookups to find what I want.


    It would be easier and quicker (not to mention way cooler) to drop the whole text into a single cell and then have a formula I could modify to cross reference and ID the data I need. Is there a function or combination of functions I could use in a formula that would allow me to specify which substring in this text string I am interested in and then use it to to cross reference with other data without dissembling the entire string?



    600357 R0 0200590 01 ELSL·· SCA DNV

  • Re: Can A Vlookup W/Search Function Work On A Substring of Text?


    Im thinking that OFFSET/INDEX(maybe)/MATCH will do this for you.


    You could use the OFFSET to ID the column to use, based on which part of the code you wanted to use, then build that into an INDEX/MATCH to find what you need


    Would be easier to put something together if you could share a sample workbook - along with a few sample answers?

  • Re: Can A Vlookup W/Search Function Work On A Substring of Text?


    Below is a simplified idea of my problem. On the left is the raw data. On the right is an example of previously completed trips that I would like to compare. The first example is my problem of the data all bunched together. For example the sub string "OLLF" (Customer ID). I would like to use to vlookup function to directly match the data on the left example against a whole series of trips to find other trips for the same customer without having to down load a whole file and clean, format, etc. In this case the fourth trip from the top, which also contains the sub string "OLLF", would be identified and the others left alone. Some times I need to do the same trick with other portions of data or even two or three parts of the data. Does this help at all?


  • Re: Can A Vlookup W/Search Function Work On A Substring of Text?


    Below is the data, a sample. Ideally I could have a formula that I could locate in the column title "Is OLLF" that would match the data in the third line with the data in the first column. As you notice the the only thing in common between the two is the Customer ID "OLLF" does that help? Sorry for the awkward borders, etc. I am not very good at this.
    [TABLE="class: grid, width: 600, align: left"]

    [tr]


    [td]

    Truck Flt Id Sq BillTo P/U Del

    [/td]


    [td][/td]


    [td][/td]


    [td]

    Is OLLF?

    [/td]


    [/tr]


    [tr]


    [td]

    51811 G3 0198002 01 OLLF·· DNV NYC

    [/td]


    [td][/td]


    [td]

    52611 B5 0193824 02 JBSGR1 WOH BOS

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    52885· D4 0191581 01 CMSWI· OMH NYC

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    53659 H1 0196334 01 OLLF·· WCR NYC

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

  • Re: Can A Vlookup W/Search Function Work On A Substring of Text?


    OK based on your sample, I came up with this. It consist of a table on the right that 1st breaks out the code into it's components, and then searches for each component in your list. Thing is, it pulls out more than 1 match, depending on which component you look at...
    [Table="width:, class:grid"]

    [tr][td]

    [/td][td]

    A

    [/td][td]

    B

    [/td][td]

    C

    [/td][td]

    D

    [/td][td]

    E

    [/td][td]

    F

    [/td][td]

    G

    [/td][td]

    H

    [/td][td]

    I

    [/td][td]

    J

    [/td][td]

    K

    [/td][td]

    L

    [/td][/tr]


    [tr][td]

    1

    [/td][td]

    Truck Flt Id Sq BillTo P/U Del

    [/td][td]

    [/td][td]

    [/td][td]

    Is OLLF?

    [/td][td]

    [/td][td]

    Truck No

    [/td][td]

    Fleet

    [/td][td]

    Trip

    [/td][td]

    Leg

    [/td][td]

    Cust

    [/td][td]

    Orig

    [/td][td]

    Dest

    [/td][/tr]


    [tr][td]

    2

    [/td][td]

    51811 G3 0198002 01 OLLF·· DNV NYC

    [/td][td]

    [/td][td]

    52611 B5 0193824 02 JBSGR1 WOH BOS

    [/td][td]

    [/td][td]

    [/td][td]

    51811

    [/td][td]

    G3

    [/td][td]

    0198002

    [/td][td]

    01

    [/td][td]

    OLLF··

    [/td][td]

    DNV

    [/td][td]

    NYC

    [/td][/tr]


    [tr][td]

    3

    [/td][td]

    [/td][td]

    [/td][td]

    52885· D4 0191581 01 CMSWI· OMH NYC

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    1

    [/td][td]

    3

    [/td][td]

    [/td][td]

    2

    [/td][/tr]


    [tr][td]

    4

    [/td][td]

    [/td][td]

    [/td][td]

    53659 H1 0196334 01 OLLF·· WCR NYC

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][/tr]


    [tr][td]

    5

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][/tr]


    [/table]


    F2=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),LEN($A2)*(COLUMN()-6)+1,LEN($A2)))
    copied across
    This is dependent on your extract starting in column F - if you start in an earlier or later column, adjust the -6 to decrease (for earlier columns) or increase for later columns
    This breaks out the components


    Then to ID the row...
    F3=IFERROR(MATCH("*"&F2&"*",$C$2:$C$5,0),"")
    As you can see from that, the 1st match for LEG is in row 1, but the 1st match for Customer is in row 3 and the 1st match for Dest is in row 2.
    You probably want Cust?


    If you need to pull in the actuals text from that cell, use this instead...
    F3=IFERROR(INDEX($C$2:$C$5,MATCH("*"&F2&"*",$C$2:$C$5,0)),"")