{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Examples and Limitations","LAMBDA Testing"],"not_rendered":{"charts":0,"images":1,"macros":0,"external_links":0,"defined_names":20,"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":"Examples and Limitations","index":0,"n_rows":35,"n_cols":6,"rows_kept":35,"cut":false,"hidden":false,"merged":[]}
["See here for historical detail: https://exceloffthegrid.com/turn-string-formula-with-evalute/"]
[]
["Can only be instantiated from Name Manager, not from cell/sheet, and workbook must be xlsm"]
["=LAMBDA(x,EVALUATE(x))"]
[]
["Length",null,"Entry Text","Formula Text","lambdaEVAL","Formula Text"]
[{"$f":"LEN(C7)","v":8},null,"FACT(10)",null,{"$f":"lambdaEVAL(C7)","v":3628800},{"$f":"_xlfn.FORMULATEXT(E7)","v":"=lambdaEVAL(C7)"}]
[]
[{"$f":"LEN(C9)","v":18},null,"LET(a,20,b,30,a+b)",null,{"$f":"lambdaEVAL(C9)","v":50},{"$f":"_xlfn.FORMULATEXT(E9)","v":"=lambdaEVAL(C9)"}]
[]
[null,null,"LET("]
[null,null,"a,20,"]
[null,null,"b,30,"]
[null,null,"a+b"]
[null,null,")"]
[{"$f":"LEN(C16)","v":18},null,{"$f":"_xlfn.TEXTJOIN(\"\",TRUE,C11:C15)","v":"LET(a,20,b,30,a+b)"},{"$f":"_xlfn.FORMULATEXT(C16)","v":"=TEXTJOIN(\"\",TRUE,C11:C15)"},{"$f":"lambdaEVAL(C16)","v":50},{"$f":"_xlfn.FORMULATEXT(E16)","v":"=lambdaEVAL(C16)"}]
[]
[null,null,"LAMBDA("]
[null,null,"x,"]
[null,null,"x+1"]
[null,null,")"]
[{"$f":"LEN(C22)","v":13},null,{"$f":"_xlfn.TEXTJOIN(\"\",TRUE,C18:C21)","v":"LAMBDA(x,x+1)"},{"$f":"_xlfn.FORMULATEXT(C22)","v":"=TEXTJOIN(\"\",TRUE,C18:C21)"},{"$f":"lambdaEVAL(C22&\"(7)\")","v":8},{"$f":"_xlfn.FORMULATEXT(E22)","v":"=lambdaEVAL(C22&\"(7)\")"}]
[]
[null,null,"CONCAT("]
[null,241,{"$f":"\"\"\"\"&_xlfn.TEXTJOIN(\"\",TRUE,CHAR(_xlfn.SEQUENCE(B25,1,65,0)))&\"\"\"\"&\",\"","v":"\"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\","},{"$f":"_xlfn.FORMULATEXT(C25)","v":"=\"\"\"\"&TEXTJOIN(\"\",TRUE,CHAR(SEQUENCE(B25,1,65,0)))&\"\"\"\"&\",\""}]
[null,null,"\"A\""]
[null,null,")"]
[{"$f":"LEN(C28)","v":255},null,{"$f":"_xlfn.TEXTJOIN(\"\",TRUE,C24:C27)","v":"CONCAT(\"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\",\"A\")"},{"$f":"_xlfn.FORMULATEXT(C28)","v":"=TEXTJOIN(\"\",TRUE,C24:C27)"},{"$f":"lambdaEVAL(C28)","v":"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"},{"$f":"_xlfn.FORMULATEXT(E28)","v":"=lambdaEVAL(C28)"}]
[]
[null,null,"CONCAT("]
[null,242,{"$f":"\"\"\"\"&_xlfn.TEXTJOIN(\"\",TRUE,CHAR(_xlfn.SEQUENCE(B31,1,65,0)))&\"\"\"\"&\",\"","v":"\"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\","},""]
[null,null,"\"A\""]
[null,null,")"]
[{"$f":"LEN(C34)","v":256},null,{"$f":"_xlfn.TEXTJOIN(\"\",TRUE,C30:C33)","v":"CONCAT(\"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\",\"A\")"},null,{"$f":"lambdaEVAL(C34)","v":{"$error":"#VALUE!"}},{"$f":"_xlfn.FORMULATEXT(E34)","v":"=lambdaEVAL(C34)"}]
["^^^^^ Total length of string must be less than 256 = 2^8"]

{"sheet":"LAMBDA Testing","index":1,"n_rows":33,"n_cols":14,"rows_kept":33,"cut":false,"hidden":false,"merged":[[29,1,32,1]]}
[null,"LAMBDA example from here: https://exceljet.net/formula/lambda-replace-characters-recursive"]
[null,"Recursive testing outside of Name Manager details here: https://www.sumproduct.com/news/article/lambda-formulaic-recursion-its-all-about-me"]
[]
[null,"Comments","Formula"]
[null,null,"LET("]
[null,null,"ReplaceChars,"]
[null,null,null,"LAMBDA("]
[null,"Self",null,null,"ME,"]
[null,"Input string",null,null,"str,"]
[null,"Double-quote wrapped list of chars to replace",null,null,"chars,"]
[null,"Char to replace with",null,null,"sub,"]
[null,"if empty, return orig str",null,null,null,"IF(chars=\"\",str,"]
[null,"Otherwise, recursively call self",null,null,null,null,"ME("]
[null,"self",null,null,null,null,null,"ME,"]
[null,"Replace first char in list with sub",null,null,null,null,null,"SUBSTITUTE(str,LEFT(chars),sub),"]
[null,"Reduce char list by left-most",null,null,null,null,null,"MID(chars,2,LEN(chars)-1),"]
[null,"Char to replace with",null,null,null,null,null,"sub"]
[null,null,null,null,null,null,")"]
[null,null,null,null,null,")"]
[null,null,null,"),"]
[null,null,"myChars,","\"!@#$%^&*()[]<>-?.,\","]
[null,null,"mySub,","\"\","]
[null,null,"ReplaceChars(ReplaceChars,myStr,myChars,mySub)"]
[null,null,")"]
[]
[null,"Length OK?","Final Formula String"]
[null,{"$f":"LEN(C27)<256","v":true},{"$f":"TRIM(CLEAN(_xlfn.TEXTJOIN(\"\",TRUE,TRIM(CLEAN(C5:H24)))))","v":"LET(ReplaceChars,LAMBDA(ME,str,chars,sub,IF(chars=\"\",str,ME(ME,SUBSTITUTE(str,LEFT(chars),sub),MID(chars,2,LEN(chars)-1),sub))),myChars,\"!@#$%^&*()[]<>-?.,\",mySub,\"\",ReplaceChars(ReplaceChars,myStr,myChars,mySub))"}]
[]
[null,null,"Inputs",null,null,"Testing Version",null,null,"Name Manager Version",null,null,null,null,"Match?"]
[null,"myStr","Better the devil you *know*",null,null,{"$f":"lambdaEVAL(SUBSTITUTE($C$27,$B$30,ADDRESS(ROW(C30),COLUMN(C30))))","v":"Better the devil you know"},null,null,{"$f":"ReplaceChars(C30,\"!@#$%^&*()[]<>-?.,\",\"\")","v":"Better the devil you know"},null,null,null,null,{"$f":"F30=I30","v":true}]
[null,null,"The ribbons were red, blue, and green.",null,null,{"$f":"lambdaEVAL(SUBSTITUTE($C$27,$B$30,ADDRESS(ROW(C31),COLUMN(C31))))","v":"The ribbons were red blue and green"},null,null,{"$f":"ReplaceChars(C31,\"!@#$%^&*()[]<>-?.,\",\"\")","v":"The ribbons were red blue and green"},null,null,null,null,{"$f":"F31=I31","v":true}]
[null,null,"It was (almost) completely empty.",null,null,{"$f":"lambdaEVAL(SUBSTITUTE($C$27,$B$30,ADDRESS(ROW(C32),COLUMN(C32))))","v":"It was almost completely empty"},null,null,{"$f":"ReplaceChars(C32,\"!@#$%^&*()[]<>-?.,\",\"\")","v":"It was almost completely empty"},null,null,null,null,true]
[null,null,"Low stock - buy now!!!",null,null,{"$f":"lambdaEVAL(SUBSTITUTE($C$27,$B$30,ADDRESS(ROW(C33),COLUMN(C33))))","v":"Low stock  buy now"},null,null,{"$f":"ReplaceChars(C33,\"!@#$%^&*()[]<>-?.,\",\"\")","v":"Low stock  buy now"},null,null,null,null,true]