{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Title","Goal","BillAttempt","MikeAttempt","Wrap Up"],"not_rendered":{"charts":0,"images":7,"macros":1,"external_links":1,"defined_names":31,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Title","index":0,"n_rows":25,"n_cols":13,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,180]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Image credit: dimitrisvetsikas1969 / Pixabay"]

{"sheet":"Goal","index":1,"n_rows":11,"n_cols":19,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Original Text","Title Formula","Date Formula","Adjusted Where","Adjust3","Adjust2","Where","January","February","March","April","May","June","July","August","September","October","November","December"]
["Trained Seal owned by Sioux 20 January 2017",{"$f":"TRIM(LEFT(A2,D2-1))","v":"Trained Seal owned by Sioux"},{"$f":"TRIM(MID(A2,D2,50))","v":"20 January 2017"},{"$f":"IF(G2=0,999,G2-IF(E2,3,IF(F2,2,0)))","v":29},{"$f":"ISNUMBER(MID(A2,G2-3,1)+0)","v":true},{"$f":"ISNUMBER(MID(A2,G2-2,1)+0)","v":true},{"$f":"_xlfn.AGGREGATE(5,6,H2:S2)","v":32},{"$f":"FIND(H$1,$A2)","v":32},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["Chevy Belair Loan Interest Paid pre Rental by GM 1 November 2015 to 21 November 2016","Chevy Belair Loan Interest Paid pre Rental by GM","1 November 2015 to 21 November 2016",50,false,true,52,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},52,{"$error":"#VALUE!"}]
["Satin Velour Waistcoat in Elvis blue December 2016 - February 2017","Satin Velour Waistcoat in Elvis blue","December 2016 - February 2017",38,false,false,38,{"$error":"#VALUE!"},54,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},38]
["Viola tuning invoice and results 6 March 2017","Viola tuning invoice and results","6 March 2017",34,false,true,36,{"$error":"#VALUE!"},{"$error":"#VALUE!"},36,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["Bail Bond Transaction Listing 21 September 2015 to 21 October 2015","Bail Bond Transaction Listing","21 September 2015 to 21 October 2015",31,true,true,34,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},34,55,{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["Deodorant Testing BAN versus Old Spice September 2015","Deodorant Testing BAN versus Old Spice","September 2015",40,false,false,40,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},40,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["Photographs of Catacombs","Photographs of Catacombs","",999,false,false,0,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["47 Piano Keys wrapped in Bacon 17 February 1965","47 Piano Keys wrapped in Bacon","17 February 1965",32,true,true,35,{"$error":"#VALUE!"},35,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["A pivoting table is not suitable for dining October 2010","A pivoting table is not suitable for dining","October 2010",45,false,false,45,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},45,{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["Their will be a lunar transit by the ISS on April 9 2017","Their will be a lunar transit by the ISS on","April 9 2017",45,false,false,45,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},45,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]

{"sheet":"BillAttempt","index":2,"n_rows":11,"n_cols":19,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Original Text","Title (Everything up to the Date)","Date","Adjusted Where","Adjust3","Adjust2","Where","January","February","March","April","May","June","July","August","September","October","November","December"]
["Trained Seal owned by Sioux 20 January 2017",{"$f":"TRIM(LEFT(A2,D2-1))","v":"Trained Seal owned by Sioux"},{"$f":"TRIM(MID(A2,D2,50))","v":"20 January 2017"},{"$f":"IF(G2=0,999,G2-IF(E2,3,IF(F2,2,0)))","v":29},{"$f":"ISNUMBER(MID(A2,G2-3,1)+0)","v":true},{"$f":"ISNUMBER(MID(A2,G2-2,1)+0)","v":true},{"$f":"_xlfn.AGGREGATE(5,6,H2:S2)","v":32},{"$f":"FIND(H$1,$A2)","v":32},{"$f":"FIND(I$1,$A2)","v":{"$error":"#VALUE!"}},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["Chevy Belair Loan Interest Paid pre Rental by GM 1 November 2015 to 21 November 2016",{"$f":"TRIM(LEFT(A3,D3-1))","v":"Chevy Belair Loan Interest Paid pre Rental by GM"},{"$f":"TRIM(MID(A3,D3,50))","v":"1 November 2015 to 21 November 2016"},{"$f":"IF(G3=0,999,G3-IF(E3,3,IF(F3,2,0)))","v":50},{"$f":"ISNUMBER(MID(A3,G3-3,1)+0)","v":false},{"$f":"ISNUMBER(MID(A3,G3-2,1)+0)","v":true},{"$f":"_xlfn.AGGREGATE(5,6,H3:S3)","v":52},{"$f":"FIND(H$1,$A3)","v":{"$error":"#VALUE!"}},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},52,{"$error":"#VALUE!"}]
["Satin Velour Waistcoat in Elvis blue December 2016 - February 2017","Satin Velour Waistcoat in Elvis blue","December 2016 - February 2017",38,false,false,38,{"$error":"#VALUE!"},54,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},38]
["Viola tuning invoice and results 6 March 2017","Viola tuning invoice and results","6 March 2017",34,false,true,36,{"$error":"#VALUE!"},{"$error":"#VALUE!"},36,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["Bail Bond Transaction Listing 21 September 2015 to 21 October 2015","Bail Bond Transaction Listing","21 September 2015 to 21 October 2015",31,true,true,34,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},34,55,{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["Deodorant Testing BAN versus Old Spice September 2015","Deodorant Testing BAN versus Old Spice","September 2015",40,false,false,40,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},40,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["Photographs of Catacombs","Photographs of Catacombs","",999,false,false,0,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["47 Piano Keys wrapped in Bacon 17 February 1965","47 Piano Keys wrapped in Bacon","17 February 1965",32,true,true,35,{"$error":"#VALUE!"},35,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["A pivoting table is not suitable for dining October 2010","A pivoting table is not suitable for dining","October 2010",45,false,false,45,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},45,{"$error":"#VALUE!"},{"$error":"#VALUE!"}]
["Their will be a lunar transit by the ISS on April 9 2017","Their will be a lunar transit by the ISS on","April 9 2017",45,false,false,45,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},45,{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"},{"$error":"#VALUE!"}]

{"sheet":"MikeAttempt","index":3,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Original Text","Title (Everything up to the Date)","Date"]
["Trained Seal owned by Sioux 20 January 2017"]
["Chevy Belair Loan Interest Paid pre Rental by GM 1 November 2015 to 21 November 2016"]
["Satin Velour Waistcoat in Elvis blue December 2016 - February 2017"]
["Viola tuning invoice and results 6 March 2017"]
["Bail Bond Transaction Listing 21 September 2015 to 21 October 2015"]
["Deodorant Testing BAN versus Old Spice September 2015"]
["Photographs of Catacombs"]
["47 Piano Keys wrapped in Bacon 17 February 1965"]
["A pivoting table is not suitable for dining October 2010"]
["Their will be a lunar transit by the ISS on April 9 2017"]

{"sheet":"Wrap Up","index":4,"n_rows":17,"n_cols":2,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["Topics in this Episode"]
[]
[null,"Bill's super wide approach:"]
[null,"Put all 12 months in separate columns"]
[null,"Use the FIND function to see if this month is in the original text"]
[null,"To find the minimum starting position, use =AGGREGATE(5,6,…"]
[null,"A few extra formulas to look for a number 2 or 3 positions before the month"]
[]
[null,"Mike's approach:"]
[null,"Use SEARCH instead of FIND. Find is case-sensitive, Search is not."]
[null,"Create an function argument array operation by specifying B13:B24 as Find_Text."]
[null,"The formula returns #VALUE! Error, but if you press F2, F9, you will see that it is returning an array."]
[null,"The first 13 functions in AGGREGATE can not handle an array, but functions 14-19 can handle an array."]
[null,"5=MIN and 15=SMALL(,1) are similar, but SMALL(,1) will work with an array."]
[null,"LOOKUP, SUMPRODUCT, CHISQ.TEST, INDEX, and AGGREGATE can handle function array arguments without Ctrl+Shift+Enter"]
[null,"Mike was smarter by looking to see if 2 characters before the Start is a number, and then grabbing 3 characters before. The extra space is eliminated by the TRIM()"]
[null,"To get the Title, use SUBSTITUTE function to get rid of the Date text in column C"]