{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Title","Data","HowToDetectChar160","HowToTypeChar160","HowToSeeFileExtension","Sheet6","Solution","Wrap Up","Set up"],"not_rendered":{"charts":0,"images":8,"macros":1,"external_links":1,"defined_names":47,"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,2087]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Image credit: braetschit / Pixabay"]

{"sheet":"Data","index":1,"n_rows":21,"n_cols":2,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
["You have to import data."]
["One or more of the columns have these problems:"]
[null," Leading & trailing spaces that won't be removed by TRIM()"]
[null,"A mostly numeric part number where the 2nd to last character is possibly an \"e\""]
[null,"A numeric part number with 16 or more digits"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Open as CSV, several change to scientific notation"]
[null,"If you rename .CSV to .TXT, you can convert to text on the way in, but Find/Replace will convert to Scientific Notation"]

{"sheet":"HowToDetectChar160","index":2,"n_rows":17,"n_cols":5,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
[" 939549d5 "]
[" 336444f8 "]
[" 781594e3 "]
[" 244562f6 "]
[" 194626e2 "]
[" 412609e6 "]
[" 504804e9 "]
[" 996549e3 "]
[" 111271d3 "]
[" 446809e3 "]
[" 540171d2 "]
[" 568699d8 "]
[" 320372e6 "]
[" 324353f2 "]
[" 682921f5 "]
[" 676701e4 "]
[" 507372f4 ",{"$f":"\"*\"&A17&\"*\"","v":"* 507372f4 *"},{"$f":"TRIM(A17)","v":" 507372f4 "},"* 507372f4 *",{"$f":"CODE(LEFT(A17,1))","v":160}]

{"sheet":"HowToTypeChar160","index":3,"n_rows":17,"n_cols":2,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["If you have a number keypad"]
[]
[null,"Hold down Alt"]
[null,"On the number keypad, press 0160"]
[null,"Let go of Alt"]
[null,"Don't freak out when nothing happens for a few seconds"]
[null,"The character 160 will appear"]
[]
[]
[]
["No number keypad"]
[]
[null,"Go to any cell"]
[null,"Type =CHAR(160) but don't press enter"]
[null,"Press F9 to evaluate the formula"]
[null,"Scoop the mostly invisible space out of the cell"]
[null,"Press Ctrl+C to copy"]

{"sheet":"HowToSeeFileExtension","index":4,"n_rows":4,"n_cols":1,"rows_kept":4,"cut":false,"hidden":false,"merged":[]}
["Win+E for Windows Explorer"]
["Press Alt to show Menus"]
["Tools, Options, View"]
["Uncheck hide extension for known file types"]

{"sheet":"Sheet6","index":5,"n_rows":17,"n_cols":5,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["798356d5","846063927371840930","798356d5","846063927371840930","Apple"]
["882354e2","123789613277434385","882354e2","123789613277434385","Banana"]
["344913f4","939643411333204947","344913f4","939643411333204947","Cherry"]
["174480d6","550279305421482249","174480d6","550279305421482249","Apple"]
["231784d6","520153511988665356","231784d6","520153511988665356","Banana"]
["830618d6","425806608641160865","830618d6","425806608641160865","Cherry"]
["389572e2","672902958109138319","389572e2","672902958109138319","Apple"]
["369096f2","509788351533607856","369096f2","509788351533607856","Banana"]
["573302f5","403422332504226404","573302f5","403422332504226404","Cherry"]
["217178f4","138375670372797184","217178f4","138375670372797184","Apple"]
["483840f6","778910538899688958","483840f6","778910538899688958","Banana"]
["567895d2","689552455164309350","567895d2","689552455164309350","Cherry"]
["497058f3","987027997430623528","497058f3","987027997430623528","Apple"]
["449844d5","923058196681897830","449844d5","923058196681897830","Banana"]
["641827d8","452337124230299031","641827d8","452337124230299031","Cherry"]
["356092f9","621422552447306026","356092f9","621422552447306026","Apple"]
["873190e8","151946639002501831","873190e8","151946639002501831","Banana"]

{"sheet":"Solution","index":6,"n_rows":12,"n_cols":1,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Excel 2013 or Earlier"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Office 365"]

{"sheet":"Wrap Up","index":7,"n_rows":17,"n_cols":2,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["Topics in this Episode"]
[]
[null,"You have spaces that TRIM won't remove"]
[null,"You have a part number that ends with e and a digit"]
[null,"You have a part number with more than 15 digits"]
[null,"If you import as a CSV file, the part numbers change to Scientific Notation"]
[null,"How to show extensions in Windows Explorer"]
[null,"If you import by opening a .txt file, you can attempt to specify those columns are text, but"]
[null,"when you find/replace the non-breaking space (character 160), the part numbers change to scientific notation"]
[null,"The solution is to use Data, Get External Data, From Text."]
[null,"However, this command is missing from Office 365, having been replaced by Get & Transform."]
[null,"If you don't have From Text, right-click the Quick Access Toolbar and Customize"]
[null,"In the top-left dropdown, change to All Commands. Find From Text (legacy) & add to QAT"]
[null,"You can open a CSV file using From Text and it will let you go through text import wizard"]
[null,"In step 2 of the wizard, specify both a comma and Alt+0160 as custom. Treat consecutive delimiters as one."]
[null,"Thanks to Jan Karel: http://jkp-ads.com/articles/importtext.asp"]
[null,"Don't forget to vote: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10374741-stop-excel-from-changing-large-numbers-actually"]

{"sheet":"Set up","index":8,"n_rows":18,"n_cols":22,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,"Steve from Indy:",null,null,null,{"$f":"CHAR(160)","v":" "},{"$f":"RANDBETWEEN(111111,999999)","v":667985},{"$f":"LOWER(CHAR(RANDBETWEEN(68,70)))","v":"f"},{"$f":"RANDBETWEEN(2,9)","v":8},{"$f":"CHAR(160)","v":" "},",",{"$f":"CHAR(160)","v":" "},{"$f":"RANDBETWEEN(111111,999999)","v":685957},{"$f":"RANDBETWEEN(111111,999999)","v":809118},{"$f":"RANDBETWEEN(111111,999999)","v":148538},{"$f":"CHAR(160)","v":" "},",",{"$f":"CHAR(160)","v":" "},"Apple",null,{"$f":"_xlfn.TEXTJOIN(\"\",FALSE,H2:J2,R2,N2:P2,R2,G2:U2)","v":"667985f8,685957809118148538, 667985f8 , 685957809118148538 , Apple"}]
[null,null,null,null,null,null,{"$f":"CHAR(160)","v":" "},{"$f":"RANDBETWEEN(111111,999999)","v":151842},{"$f":"LOWER(CHAR(RANDBETWEEN(68,70)))","v":"d"},{"$f":"RANDBETWEEN(2,9)","v":5},{"$f":"CHAR(160)","v":" "},","," ",{"$f":"RANDBETWEEN(111111,999999)","v":919961},125960,240197,{"$f":"CHAR(160)","v":" "},","," ","Banana",null,{"$f":"_xlfn.TEXTJOIN(\"\",FALSE,H3:J3,R3,N3:P3,R3,G3:U3)","v":"151842d5,919961125960240197, 151842d5 , 919961125960240197 , Banana"}]
[null,null,"Is there a way to fill dates in ALL CAPS?",null,null,null," ",462449,"f",7," ",","," ",296511,824576,448002," ",","," ","Cherry",null,"462449f7,296511824576448002, 462449f7 , 296511824576448002 , Cherry"]
[null,null,null,null,null,null," ",938659,"f",9," ",","," ",695376,710557,463870," ",","," ","Apple",null,"938659f9,695376710557463870, 938659f9 , 695376710557463870 , Apple"]
[null,null,null,null,null,null," ",558832,"f",9," ",","," ",251463,586714,365302," ",","," ","Banana",null,"558832f9,251463586714365302, 558832f9 , 251463586714365302 , Banana"]
[null,null,null,null,null,null," ",783885,"f",7," ",","," ",273757,619098,631153," ",","," ","Cherry",null,"783885f7,273757619098631153, 783885f7 , 273757619098631153 , Cherry"]
[null,null,null,null,null,null," ",384061,"f",9," ",","," ",691171,801727,726392," ",","," ","Apple",null,"384061f9,691171801727726392, 384061f9 , 691171801727726392 , Apple"]
[null,null,null,null,null,null," ",323058,"d",8," ",","," ",147165,961939,660806," ",","," ","Banana",null,"323058d8,147165961939660806, 323058d8 , 147165961939660806 , Banana"]
[null,null,null,null,null,null," ",514724,"e",2," ",","," ",790105,651952,528953," ",","," ","Cherry",null,"514724e2,790105651952528953, 514724e2 , 790105651952528953 , Cherry"]
[null,null,null,null,null,null," ",526948,"f",7," ",","," ",746743,787947,786439," ",","," ","Apple",null,"526948f7,746743787947786439, 526948f7 , 746743787947786439 , Apple"]
[null,null,null,null,null,null," ",874204,"d",9," ",","," ",818472,144290,324636," ",","," ","Banana",null,"874204d9,818472144290324636, 874204d9 , 818472144290324636 , Banana"]
[null,null,null,null,null,null," ",972533,"f",7," ",","," ",569805,606074,936551," ",","," ","Cherry",null,"972533f7,569805606074936551, 972533f7 , 569805606074936551 , Cherry"]
[null,null,null,null,null,null," ",218996,"f",8," ",","," ",566869,723494,293499," ",","," ","Apple",null,"218996f8,566869723494293499, 218996f8 , 566869723494293499 , Apple"]
[null,null,null,null,null,null," ",284038,"e",9," ",","," ",762901,225485,600896," ",","," ","Banana",null,"284038e9,762901225485600896, 284038e9 , 762901225485600896 , Banana"]
[null,null,null,null,null,null," ",777415,"d",4," ",","," ",183534,956631,517382," ",","," ","Cherry",null,"777415d4,183534956631517382, 777415d4 , 183534956631517382 , Cherry"]
[null,null,null,null,null,null," ",932397,"e",6," ",","," ",186076,154007,136081," ",","," ","Apple",null,"932397e6,186076154007136081, 932397e6 , 186076154007136081 , Apple"]
[null,null,null,null,null,null," ",224422,"e",4," ",","," ",385433,548619,177924," ",","," ","Banana",null,"224422e4,385433548619177924, 224422e4 , 385433548619177924 , Banana"]