{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["LAMBDA function - Examples","Nested SUBSTITUTE","XLOOKUP","MultiReplace LAMBDA","ReplaceChars LAMBDA","MassReplace UDF","Bulk replace with macro"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":0,"defined_names":15,"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":"LAMBDA function - Examples","index":0,"n_rows":20,"n_cols":7,"rows_kept":20,"cut":false,"hidden":false,"merged":[[15,1,15,2],[14,1,14,2],[3,1,3,2],[5,1,5,2],[11,1,11,2],[12,1,12,2],[13,1,13,2]]}
[]
[]
[]
[null,"Sample Workbook to Bulk Replace in Excel"]
[]
[null,"The workbook shows how to write and use custom LAMBDA functions in Excel."]
[null,"Author","Ablebits.com"]
[null,"Last update","2021-06-17"]
[null,"Tutorial URL","How to find and replace multiple values in Excel"]
[]
[null,"Examples:"]
["• ","Nested SUBSTITUTE to replace multiple values "]
["• ","XLOOKUP to find and replace multiple words"]
["• ","LAMBDA function to search and replace multiple words "]
["• ","LAMBDA function to replace multiple characters "]
["• ","Mass find and replace with UDF "]
["• ","Bulk replace with VBA macro"]
[]
[]
[null,null,null,null,null,null," "]

{"sheet":"Nested SUBSTITUTE","index":1,"n_rows":10,"n_cols":5,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Source data","Result",null,"Old ","New"]
["USA, NC",{"$f":"SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A10, D2, E2), D3, E3), D4, E4)","v":"United States, NC"},null,"FR","France"]
["UK, Avon","United Kingdom, Avon",null,"UK","United Kingdom"]
["USA, IL","United States, IL",null,"USA","United States"]
["USA, KS","United States, KS"]
["UK, Blackpool","United Kingdom, Blackpool"]
["USA, MO","United States, MO"]
["FR, Bourgogne","France, Bourgogne"]
["USA, AZ","United States, AZ"]
["UK, Hampshire","United Kingdom, Hampshire"]

{"sheet":"XLOOKUP","index":2,"n_rows":23,"n_cols":9,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
["Source data","Result",null,"Find","Replace"]
["USA",{"$f":"_xlfn.XLOOKUP(A2, $D$2:$D$4, $E$2:$E$4, A2)","v":"United States"},null,"FR","France"]
["Poland",{"$f":"_xlfn.XLOOKUP(A3, $D$2:$D$4, $E$2:$E$4, A3)","v":"Poland"},null,"UK","United Kingdom"]
["UK","United Kingdom",null,"USA","United States"]
["FR","France"]
["USA","United States"]
["UK","United Kingdom"]
["Germany","Germany",null,null,null," "]
["USA","United States"]
["Norway","Norway"]
[]
[]
[]
[]
[]
[]
[null,null,null,null," "]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null," "]

{"sheet":"MultiReplace LAMBDA","index":3,"n_rows":60,"n_cols":5,"rows_kept":60,"cut":false,"hidden":false,"merged":[]}
["Source data","Result",null,"Old ","New"]
["USA, NC",{"$f":"MultiReplace(A2:A10, D2, E2)","v":"United States, North Carolina"},null,"FR","France"]
["UK, Avon","United Kingdom, Avon",null,"UK","United Kingdom"]
["USA, IL","United States, Illinois",null,"USA","United States"]
["USA, KS","United States, Kansas",null,"AL","Alabama"]
["UK, Blackpool","United Kingdom, Blackpool",null,"AK","Alaska"]
["USA, MO","United States, Missouri",null,"AS","American Samoa"]
["FR, Bourgogne","France, Bourgogne",null,"AZ","Arizona"]
["USA, AZ","United States, Arizona",null,"AR","Arkansas"]
["UK, Hampshire","United Kingdom, Hampshire",null,"CA","California"]
[null,null,null,"CO","Colorado"]
[null,null,null,"CT","Connecticut"]
[null,null,null,"DE","Delaware"]
[null,null,null,"DC","District Of Columbia"]
[null,null,null,"FL","Florida"]
[null,null,null,"GA","Georgia"]
[null,null,null,"GU","Guam"]
[null,null,null,"HI","Hawaii"]
[null,null,null,"ID","Idaho"]
[null,null,null,"IL","Illinois"]
[null,null,null,"IN","Indiana"]
[null,null,null,"IA","Iowa"]
[null,null,null,"KS","Kansas"]
[null,null,null,"KY","Kentucky"]
[null,null,null,"LA","Louisiana"]
[null,null,null,"ME","Maine"]
[null,null,null,"MD","Maryland"]
[null,null,null,"MA","Massachusetts"]
[null,null,null,"MI","Michigan"]
[null,null,null,"MN","Minnesota"]
[null,null,null,"MS","Mississippi"]
[null,null,null,"MO","Missouri"]
[null,null,null,"MT","Montana"]
[null,null,null,"NE","Nebraska"]
[null,null,null,"NV","Nevada"]
[null,null,null,"NH","New Hampshire"]
[null,null,null,"NJ","New Jersey"]
[null,null,null,"NM","New Mexico"]
[null,null,null,"NY","New York"]
[null,null,null,"NC","North Carolina"]
[null,null,null,"ND","North Dakota"]
[null,null,null,"MP","Northern Mariana Is"]
[null,null,null,"OH","Ohio"]
[null,null,null,"OK","Oklahoma"]
[null,null,null,"OR","Oregon"]
[null,null,null,"PA","Pennsylvania"]
[null,null,null,"PR","Puerto Rico"]
[null,null,null,"RI","Rhode Island"]
[null,null,null,"SC","South Carolina"]
[null,null,null,"SD","South Dakota"]
[null,null,null,"TN","Tennessee"]
[null,null,null,"TX","Texas"]
[null,null,null,"UT","Utah"]
[null,null,null,"VT","Vermont"]
[null,null,null,"VA","Virginia"]
[null,null,null,"VI","Virgin Islands"]
[null,null,null,"WA","Washington"]
[null,null,null,"WV","West Virginia"]
[null,null,null,"WI","Wisconsin"]
[null,null,null,"WY","Wyoming"]

{"sheet":"ReplaceChars LAMBDA","index":4,"n_rows":4,"n_cols":5,"rows_kept":4,"cut":false,"hidden":false,"merged":[]}
["Source data","Result",null,"Old chars","New chars"]
["Let’s check “Feature 1” ",{"$f":"ReplaceChars(A2:A4, D2, E2)","v":"Let's check \"Feature 1\" "},null,"“ ” ’","\" \" '"]
["“Feature 2” is nice","\"Feature 2\" is nice"]
["That’s perfect!","That's perfect!"]

{"sheet":"MassReplace UDF","index":5,"n_rows":37,"n_cols":11,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
["Source data","Result",null,"Find range","Replace range"]
["USA, NC",{"$f":"MassReplace(A2:A10, D2:D4, E2:E4)","v":"United States, NC"},null,"FR","France"]
["UK, Avon","United Kingdom, Avon",null,"UK","United Kingdom"]
["USA, IL","United States, IL",null,"USA","United States"]
["USA, KS","United States, KS"]
["UK, Blackpool","United Kingdom, Blackpool"]
["USA, MO","United States, MO"]
["FR, Bourgogne","France, Bourgogne",null,null,null," "]
["USA, AZ","United States, AZ"]
["UK, Hampshire","United Kingdom, Hampshire"]
[]
[]
[]
[]
[]
[]
[null,null,null,null," "]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null," "]

{"sheet":"Bulk replace with macro","index":6,"n_rows":19,"n_cols":7,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
["Source data",null,"Old ","New"]
["USA, NC",null,"FR","France",null,null,"How to do bulk replace using a macro:"]
["UK, Avon",null,"UK","United Kingdom",null,null,"1. Press Alt + F8, select the BulkReplace macro, and click Run."]
["USA, IL",null,"USA","United States",null,null,"2. For the Source range, select A2;A10, and click OK."]
["USA, KS",null,null,null,null,null,"3. For the Replace range, select C2:D4, and click OK."]
["UK, Blackpool"]
["USA, MO"]
["FR, Bourgogne"]
["USA, AZ"]
["UK, Hampshire"]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null," "]