{"text_format":"1","workbook":"xlsx","n_sheets":13,"sheets":["dataset","quick view","non-printable","line break","extra space","SUBSTITUTE","LEN","LEFT","Replace","check","value","VBA","practice"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"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":"dataset","index":0,"n_rows":10,"n_cols":11,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,4]]}
[]
[null,"Student's of 'XYZ' College",null,null,null,null,null,null,null,null,{"$f":"CHAR(160)","v":" "}]
[]
[null,"Student Id","Student Name","Imported Marks","Imported Email Id"]
[null,"XYZ2015A01","Michael\n James","90\u0013","\u0013james@gmail.com\u0013"]
[null,"XYZ2013B01","John \nSteves","8\u00135","steves@gmail.com\f",null,null,{"$f":"CHAR(160)","v":" "}]
[null,"XYZ2015C04","John\n Henry","78\f","john \u0013@gmail.com"]
[null,"XYZ2016A02","Michael\n Robert","68\f","robert@gmail.com\u0013"]
[null,"XYZ2012A08","Michael\n Peter","5\u00135","\u0013michael@gmail.com"]
[null,"XYZ2016B05","William \nJonas","67\u0013","\u0013william@gmail.com\u0013"]

{"sheet":"quick view","index":1,"n_rows":12,"n_cols":4,"rows_kept":12,"cut":false,"hidden":false,"merged":[[1,1,1,3],[2,1,2,3],[11,1,11,3]]}
[]
[null,"CLEAN Function"]
[null,"=CLEAN(text)"]
[]
[null,"Text","Formula","Output"]
[null,"\u0013james@gmail.com\u0013",{"$f":"_xlfn.FORMULATEXT(D6)","v":"=CLEAN(B6)"},{"$f":"CLEAN(B6)","v":"james@gmail.com"}]
[null,"steves@gmail.com\f",{"$f":"_xlfn.FORMULATEXT(D7)","v":"=CLEAN(B7)"},{"$f":"CLEAN(B7)","v":"steves@gmail.com"}]
[null,"john \u0013@gmail.com","=CLEAN(B8)",{"$f":"CLEAN(B8)","v":"john @gmail.com"}]
[null,"robert\n@gmail.com","=CLEAN(B9)",{"$f":"CLEAN(B9)","v":"robert@gmail.com"}]
[null,"\u0013michael@gmail.com","=CLEAN(B10)",{"$f":"CLEAN(B10)","v":"michael@gmail.com"}]
[]
[null,"CLEAN Function removes all non-printable Characters and line break"]

{"sheet":"non-printable","index":2,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"Removing Non-Printable Characters"]
[]
[null,"Student Id","Imported Email Id","Real Email Id"]
[null,"XYZ2015A01","\u0013james@gmail.com\u0013",{"$f":"CLEAN(C5)","v":"james@gmail.com"}]
[null,"XYZ2013B01","steves@gmail.com\f",{"$f":"CLEAN(C6)","v":"steves@gmail.com"}]
[null,"XYZ2015C04","john \u0013@gmail.com","john @gmail.com"]
[null,"XYZ2016A02","robert@gmail.com\u0013","robert@gmail.com"]
[null,"XYZ2012A08","\u0013michael@gmail.com","michael@gmail.com"]
[null,"XYZ2016B05","\u0013william@gmail.com\u0013","william@gmail.com"]

{"sheet":"line break","index":3,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"Removing Line Break"]
[]
[null,"Student Id","Student Name","Student Name without line break"]
[null,"XYZ2015A01","Michael\n James",{"$f":"CLEAN(C5)","v":"Michael James"}]
[null,"XYZ2013B01","John \nSteves",{"$f":"CLEAN(C6)","v":"John Steves"}]
[null,"XYZ2015C04","John\n Henry","John Henry"]
[null,"XYZ2016A02","Michael\n Robert","Michael Robert"]
[null,"XYZ2012A08","Michael\n Peter","Michael Peter"]
[null,"XYZ2016B05","William \nJonas","William Jonas"]

{"sheet":"extra space","index":4,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"Removing extra space"]
[]
[null,"Student Id","Imported Email Id","Real Email Id"]
[null,"XYZ2015A01","\u0013james@gmail.com\u0013",{"$f":"TRIM(CLEAN(C5))","v":"james@gmail.com"}]
[null,"XYZ2013B01"," steves@gmail.com\f",{"$f":"TRIM(CLEAN(C6))","v":"steves@gmail.com"}]
[null,"XYZ2015C04","   john \u0013@gmail.com","john @gmail.com"]
[null,"XYZ2016A02","      robert@gmail.com\u0013","robert@gmail.com"]
[null,"XYZ2012A08","\u0013michael@gmail.com   ","michael@gmail.com"]
[null,"XYZ2016B05","\u0013william@gmail.com\u0013","william@gmail.com"]

{"sheet":"SUBSTITUTE","index":5,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"Using SUBSTITUTE Function"]
[]
[null,"Student Id","Imported Email Id","Real Email Id"]
[null,"XYZ2015A01","\u0013james@gmail.com\u0013",{"$f":"TRIM(CLEAN(SUBSTITUTE(C5,CHAR(160),\"\")))","v":"james@gmail.com"}]
[null,"XYZ2013B01"," steves@gmail.com\f",{"$f":"TRIM(CLEAN(SUBSTITUTE(C6,CHAR(160),\"\")))","v":"steves@gmail.com"}]
[null,"XYZ2015C04","   john \u0013@gmail.com","john @gmail.com"]
[null,"XYZ2016A02","      robert@gmail.com\u0013","robert@gmail.com"]
[null,"XYZ2012A08","\u0013michael@gmail.com   ","michael@gmail.com"]
[null,"XYZ2016B05","\u0013william@gmail.com\u0013","william@gmail.com"]

{"sheet":"LEN","index":6,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"Number of Characters"]
[]
[null,"Student Id","Student Name","Number of Character in Name"]
[null,"XYZ2015A01","Michael\n James",{"$f":"LEN(TRIM(CLEAN(C5)))","v":13}]
[null,"XYZ2013B01","John \nSteves",{"$f":"LEN(TRIM(CLEAN(C6)))","v":11}]
[null,"XYZ2015C04","John\n Henry",10]
[null,"XYZ2016A02","Michael\n Robert",14]
[null,"XYZ2012A08","Michael\n Peter",13]
[null,"XYZ2016B05","William \nJonas",13]

{"sheet":"LEFT","index":7,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"Extracting Characters"]
[]
[null,"Student Id","Imported Email Id","First Name"]
[null,"XYZ2015A01","\u0013james@gmail.com\u0013",{"$f":"LEFT(CLEAN(C5),FIND(\"@\",CLEAN(C5),1)-1)","v":"james"}]
[null,"XYZ2013B01","steves@gmail.com\f",{"$f":"LEFT(CLEAN(C6),FIND(\"@\",CLEAN(C6),1)-1)","v":"steves"}]
[null,"XYZ2015C04","john \u0013@gmail.com","john "]
[null,"XYZ2016A02","robert@gmail.com\u0013","robert"]
[null,"XYZ2012A08","\u0013michael@gmail.com","michael"]
[null,"XYZ2016B05","\u0013william@gmail.com\u0013","william"]

{"sheet":"Replace","index":8,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"Replacing Characters"]
[]
[null,"Student Id","Imported Email Id","New Email Id"]
[null,"XYZ2015A01","\u0013james@gmail.com\u0013",{"$f":"SUBSTITUTE(CLEAN(C5),\"gmail\",\"yahoo\")","v":"james@yahoo.com"}]
[null,"XYZ2013B01","steves@gmail.com\f",{"$f":"SUBSTITUTE(CLEAN(C6),\"gmail\",\"yahoo\")","v":"steves@yahoo.com"}]
[null,"XYZ2015C04","john \u0013@gmail.com","john @yahoo.com"]
[null,"XYZ2016A02","robert@gmail.com\u0013","robert@yahoo.com"]
[null,"XYZ2012A08","\u0013michael@gmail.com","michael@yahoo.com"]
[null,"XYZ2016B05","\u0013william@gmail.com\u0013","william@yahoo.com"]

{"sheet":"check","index":9,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"Checking if Cleaning needed"]
[]
[null,"Student Id","Imported Email Id","Check"]
[null,"XYZ2015A01","\u0013james@gmail.com\u0013",{"$f":"IF(CLEAN(C5)=C5,\"Cleaned\",\"Not Cleaned\")","v":"Not Cleaned"}]
[null,"XYZ2013B01","steves@gmail.com\f",{"$f":"IF(CLEAN(C6)=C6,\"Cleaned\",\"Not Cleaned\")","v":"Not Cleaned"}]
[null,"XYZ2015C04","john \u0013@gmail.com","Not Cleaned"]
[null,"XYZ2016A02","robert@gmail.com\u0013","Not Cleaned"]
[null,"XYZ2012A08","michael@gmail.com","Cleaned"]
[null,"XYZ2016B05","\u0013william@gmail.com\u0013","Not Cleaned"]

{"sheet":"value","index":10,"n_rows":11,"n_cols":4,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"Averaging Values"]
[]
[null,"Student Id","Imported Marks","Marks"]
[null,"XYZ2015A01","90\u0013",{"$f":"VALUE(CLEAN(C5))","v":90}]
[null,"XYZ2013B01","8\u00135",{"$f":"VALUE(CLEAN(C6))","v":85}]
[null,"XYZ2015C04","78\f",78]
[null,"XYZ2016A02","68\f",68]
[null,"XYZ2012A08","5\u00135",55]
[null,"XYZ2016B05","67\u0013",67]
[null,null,"Average Marks",{"$f":"AVERAGE(D5:D10)","v":73.833333333333329}]

{"sheet":"VBA","index":11,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,3]]}
[]
[null,"VBA Code"]
[]
[null,"Student Id","Imported Email Id","Real Email Id"]
[null,"XYZ2015A01","\u0013james@gmail.com\u0013","james@gmail.com"]
[null,"XYZ2013B01","steves@gmail.com\f","steves@gmail.com"]
[null,"XYZ2015C04","john \u0013@gmail.com","john @gmail.com"]
[null,"XYZ2016A02","robert@gmail.com\u0013","robert@gmail.com"]
[null,"XYZ2012A08","\u0013michael@gmail.com","michael@gmail.com"]
[null,"XYZ2016B05","\u0013william@gmail.com\u0013","william@gmail.com"]

{"sheet":"practice","index":12,"n_rows":10,"n_cols":5,"rows_kept":10,"cut":false,"hidden":false,"merged":[[1,1,1,4]]}
[]
[null,"Practice"]
[]
[null,"Student Id","Student Name","Imported Marks","Imported Email Id"]
[null,"XYZ2015A01","Michael\n James","90\u0013","\u0013james@gmail.com\u0013"]
[null,"XYZ2013B01","John \nSteves","8\u00135","steves@gmail.com\f"]
[null,"XYZ2015C04","John\n Henry","78\f","john \u0013@gmail.com"]
[null,"XYZ2016A02","Michael\n Robert","68\f","robert@gmail.com\u0013"]
[null,"XYZ2012A08","Michael\n Peter","5\u00135","\u0013michael@gmail.com"]
[null,"XYZ2016B05","William \nJonas","67\u0013","\u0013william@gmail.com\u0013"]