In it's most simplest form my need is simple... I have a cell that contains a "note field". I would like to search inside this note field and pull out a date that is before a specific sub-string. It gets tricky when I start to break it down and try to actually make it happen, however.
The data I need to extract is in this format at random places within the string: "4/21/14 EMAIL QT RECD". Sometimes it starts at the very first character, sometimes its 100 characters in.
A couple examples of note fields:
Example 1:
4/17/14 EMAIL QT RECD, NON STD OVHL, QT NOT APPROVED, COMPANY WILL PROVIDE PIECE PARTS. DETAILS: RECD MISSING HYDRAULIC INLET, 3 TIE BOLTS, 2 TIE BOLT NUTS, 1 BLEEDER SCREW, 4 TORQUE LINER SCREWS, 3 ADJUSTER PIN NUTS FAILED INSPECTION, PISTON HOUSING REQUIRES CORROSION REMOVAL, QT FOR ROUTINE PARTS & LABOR USING OEM PARTS $1234.56 + NON ROUTINE PARTS $123.45, BB 123456 $1234.56, QT STILL NOT APPROVED (AB).
Example 2:
** PLEASE EXPEDITE THE EVALUATION, INSPECT THOROUGHLY FOR CRACKS & DEFECTS (EARLY SERIAL NUMBER WHEEL) ** QUOTE OVERHAUL INCLUDING NEW TIRE (COMPANY PN: 12345-6). IN THIS SHIPMENT (ALL USED): WHEEL, HARDWARE, TIRE, SLEEVE. ** MISSING / NOT INCLUDED: BEARING CONES, SEALS, RETAINING CLIPS ** 12/23/15 EMAIL QT RECD, NON STD OVHL, REQURIES 3 EA PACKING $1.00 + DECAL $2.00 + TIRE $3.00 + WHEEL HALF $4.00 + 2 RING $5.00 + 2 BEARING $6.00 + 2 SEALS $7.00 + PACKING $8.00, QT NOT APPROVED (AB). ** SEE GLOBAL S.O. 123456 NOTES FOR FULL CUSTOMER DETAILS** 12/29/15 PER CD, COMPANY CAN PROVIDE IB WHEEL HALF PN 1234-567-8 AND WE CAN REDUCE BB TO $1.00 (WHEEL HALF) + $2.00 (MISSING SEALS) = $3.00 (AB). 1/22/16 CUSTOMER USED UP TIME ALLOWED TO PROVIDE REPLACEMENT CORE OR IB WHEEL HALF, FINALIZING BB TO 123456 AT $3.00 (AB). 1/22/16 QUOTE NOT YET APPROVED, CHECKING STATUS OF COMPANY PROVIDED IB WHEEL HALF WITH CD (AB). UPDATE: 4/25/16 EMAILED CUSTOMER REMINDER THAT WE NEED GOOD HALF OVERHAULED AND RETURNED, ALL REMAINING PARTS TO BE SEPARATED GOOD/BAD (AB). 4/27/16 APPROVED QT FOR $4.00 ONLY AND REQUESTED OTB WHEEL HALF BE RETURNED OVERHAULED WITH 8130-3, AND ALL REMAINING PARTS MARKED EITHER GOOD OR BAD (AB).
I got pretty close to what I wanted by scouring forums and eventually slightly modifying the following formula:
=MID(N1,SEARCH("EMAIL QT RECD",N1)-8,SEARCH(" ",N1,SEARCH("EMAIL QT RECD",N1)-8)-(SEARCH("EMAIL QT RECD",N1)-8))
A few problems with this solution; it cuts off the first digit of dates formatted as MM/DD/YY instead of M/DD/YY. Also, if the date is formatted as M/D/YY and is the first character in the string it returns #VALUE !.
I've tried playing around with it to make it work but I just can't get it there.
I'm using Excel 2010 on Windows 7 but this solution will eventually be used by coworkers running newer versions of Excel as well.
If I can get this part worked out I would also like to be able to extract other dates as well from within the same note field by searching for other sub-strings. For example, "APPROVED QT, QUOTE APPROVED, QUOTE NOT APPROVED, ETA SENT etc. I would like the date of whichever string is found last in the search to show in the result cell.
An example of this would be:
1/30/15 EMAIL QT RECD, PART WILL COST $X TO REPAIR
2/7/15 QT APPROVED, EMAILED SHOP FOR STANDARD OVERHAUL
2/25/15 ETA SENT, SHOP WILL RETURN PART ON 3/15/15
The return result would be 2/25/15 even though all three strings are found in the search.
I know this is a lot and it's complicated but I've banged my head on just the first part for the past week and half. So I'm hoping someone with more experience and know-how can help me out on this one. At least push me off in the right direction maybe! Thanks!
The data I need to extract is in this format at random places within the string: "4/21/14 EMAIL QT RECD". Sometimes it starts at the very first character, sometimes its 100 characters in.
A couple examples of note fields:
Example 1:
4/17/14 EMAIL QT RECD, NON STD OVHL, QT NOT APPROVED, COMPANY WILL PROVIDE PIECE PARTS. DETAILS: RECD MISSING HYDRAULIC INLET, 3 TIE BOLTS, 2 TIE BOLT NUTS, 1 BLEEDER SCREW, 4 TORQUE LINER SCREWS, 3 ADJUSTER PIN NUTS FAILED INSPECTION, PISTON HOUSING REQUIRES CORROSION REMOVAL, QT FOR ROUTINE PARTS & LABOR USING OEM PARTS $1234.56 + NON ROUTINE PARTS $123.45, BB 123456 $1234.56, QT STILL NOT APPROVED (AB).
Example 2:
** PLEASE EXPEDITE THE EVALUATION, INSPECT THOROUGHLY FOR CRACKS & DEFECTS (EARLY SERIAL NUMBER WHEEL) ** QUOTE OVERHAUL INCLUDING NEW TIRE (COMPANY PN: 12345-6). IN THIS SHIPMENT (ALL USED): WHEEL, HARDWARE, TIRE, SLEEVE. ** MISSING / NOT INCLUDED: BEARING CONES, SEALS, RETAINING CLIPS ** 12/23/15 EMAIL QT RECD, NON STD OVHL, REQURIES 3 EA PACKING $1.00 + DECAL $2.00 + TIRE $3.00 + WHEEL HALF $4.00 + 2 RING $5.00 + 2 BEARING $6.00 + 2 SEALS $7.00 + PACKING $8.00, QT NOT APPROVED (AB). ** SEE GLOBAL S.O. 123456 NOTES FOR FULL CUSTOMER DETAILS** 12/29/15 PER CD, COMPANY CAN PROVIDE IB WHEEL HALF PN 1234-567-8 AND WE CAN REDUCE BB TO $1.00 (WHEEL HALF) + $2.00 (MISSING SEALS) = $3.00 (AB). 1/22/16 CUSTOMER USED UP TIME ALLOWED TO PROVIDE REPLACEMENT CORE OR IB WHEEL HALF, FINALIZING BB TO 123456 AT $3.00 (AB). 1/22/16 QUOTE NOT YET APPROVED, CHECKING STATUS OF COMPANY PROVIDED IB WHEEL HALF WITH CD (AB). UPDATE: 4/25/16 EMAILED CUSTOMER REMINDER THAT WE NEED GOOD HALF OVERHAULED AND RETURNED, ALL REMAINING PARTS TO BE SEPARATED GOOD/BAD (AB). 4/27/16 APPROVED QT FOR $4.00 ONLY AND REQUESTED OTB WHEEL HALF BE RETURNED OVERHAULED WITH 8130-3, AND ALL REMAINING PARTS MARKED EITHER GOOD OR BAD (AB).
I got pretty close to what I wanted by scouring forums and eventually slightly modifying the following formula:
=MID(N1,SEARCH("EMAIL QT RECD",N1)-8,SEARCH(" ",N1,SEARCH("EMAIL QT RECD",N1)-8)-(SEARCH("EMAIL QT RECD",N1)-8))
A few problems with this solution; it cuts off the first digit of dates formatted as MM/DD/YY instead of M/DD/YY. Also, if the date is formatted as M/D/YY and is the first character in the string it returns #VALUE !.
I've tried playing around with it to make it work but I just can't get it there.
I'm using Excel 2010 on Windows 7 but this solution will eventually be used by coworkers running newer versions of Excel as well.
If I can get this part worked out I would also like to be able to extract other dates as well from within the same note field by searching for other sub-strings. For example, "APPROVED QT, QUOTE APPROVED, QUOTE NOT APPROVED, ETA SENT etc. I would like the date of whichever string is found last in the search to show in the result cell.
An example of this would be:
1/30/15 EMAIL QT RECD, PART WILL COST $X TO REPAIR
2/7/15 QT APPROVED, EMAILED SHOP FOR STANDARD OVERHAUL
2/25/15 ETA SENT, SHOP WILL RETURN PART ON 3/15/15
The return result would be 2/25/15 even though all three strings are found in the search.
I know this is a lot and it's complicated but I've banged my head on just the first part for the past week and half. So I'm hoping someone with more experience and know-how can help me out on this one. At least push me off in the right direction maybe! Thanks!
, and it ramps the complexity up quite a bit. I can also guarantee that it still won't fix everything, but it should catch some extraneous characters.
