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