{"text_format":"2","workbook":"xlsx","n_sheets":1,"sheets":["Normalize Single Pass"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"run_cost":"one","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":"Normalize Single Pass","index":0,"n_rows":18,"n_cols":36,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["Clean Mailing Addresses - Tony Trabucco; Revised 11/24/2019"]
[]
["Input Column","Results/Output",null,null,"North",{"$f":"CONCATENATE(LEFT(E3,1),\".\")","v":"N."},{"$f":"CONCATENATE(LEFT(E3,2),\".\")","v":"No."},"South",{"$f":"CONCATENATE(LEFT(H3,1),\".\")","v":"S."},{"$f":"CONCATENATE(LEFT(H3,2),\".\")","v":"So."},"East","E.","West","W.",null,"Street",{"$f":"CONCATENATE(LEFT(P3,1),\"t.\")","v":"St."},"Avenue",{"$f":"CONCATENATE(LEFT(R3,3),\".\")","v":"Ave."},"Boulevard","Blvd.","Drive","Dr.","Lane","Court","Ct.","Place","Pl.","Road","Rd.","Rd;","P.O.","P. O.","Po Box","Circle","Cir."]
["Addresses","Cut/Paste as Values",null,"Proper Capitalization",{"$f":"LEFT(E3,1)","v":"N"},{"$f":"E4","v":"N"},{"$f":"E4","v":"N"},{"$f":"LEFT(H3,1)","v":"S"},{"$f":"H4","v":"S"},{"$f":"H4","v":"S"},"E","E","W","W",null,{"$f":"LEFT(P3,2)","v":"St"},{"$f":"P4","v":"St"},{"$f":"LEFT(R3,3)","v":"Ave"},{"$f":"LEFT(S3,3)","v":"Ave"},"Blvd","Blvd","Dr","Dr","Ln","Ct","Ct","Pl","Pl","Rd","Rd","Rd;","PO","PO","PO Box","Cir","Cir"]
["P.O. Box 802",{"$f":"AJ5","v":"PO Box 802"},null,{"$f":"PROPER(A5)","v":"P.O. Box 802"},{"$f":"SUBSTITUTE(D5,E$3,E$4)","v":"P.O. Box 802"},"P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802",null,{"$f":"SUBSTITUTE(N5,P$3,P$4)","v":"P.O. Box 802"},{"$f":"SUBSTITUTE(P5,Q$3,Q$4)","v":"P.O. Box 802"},"P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","P.O. Box 802","PO Box 802","PO Box 802","PO Box 802","PO Box 802","PO Box 802"]
["1940 N Tustin St #107","1940 N Tustin St #107",null,"1940 N Tustin St #107",{"$f":"SUBSTITUTE(D6,E$3,E$4)","v":"1940 N Tustin St #107"},"1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107",null,{"$f":"SUBSTITUTE(N6,P$3,P$4)","v":"1940 N Tustin St #107"},{"$f":"SUBSTITUTE(P6,Q$3,Q$4)","v":"1940 N Tustin St #107"},"1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107","1940 N Tustin St #107"]
["1940 N Tustin St Ste 107","1940 N Tustin St Ste 107",null,"1940 N Tustin St Ste 107",{"$f":"SUBSTITUTE(D7,E$3,E$4)","v":"1940 N Tustin St Ste 107"},"1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107",null,{"$f":"SUBSTITUTE(N7,P$3,P$4)","v":"1940 N Tustin St Ste 107"},{"$f":"SUBSTITUTE(P7,Q$3,Q$4)","v":"1940 N Tustin St Ste 107"},"1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107","1940 N Tustin St Ste 107"]
["P.O. Box 5484","PO Box 5484",null,"P.O. Box 5484",{"$f":"SUBSTITUTE(D8,E$3,E$4)","v":"P.O. Box 5484"},"P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484",null,{"$f":"SUBSTITUTE(N8,P$3,P$4)","v":"P.O. Box 5484"},{"$f":"SUBSTITUTE(P8,Q$3,Q$4)","v":"P.O. Box 5484"},"P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","P.O. Box 5484","PO Box 5484","PO Box 5484","PO Box 5484","PO Box 5484","PO Box 5484"]
["Po Box 5484","PO Box 5484",null,"Po Box 5484",{"$f":"SUBSTITUTE(D9,E$3,E$4)","v":"Po Box 5484"},"Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484",null,{"$f":"SUBSTITUTE(N9,P$3,P$4)","v":"Po Box 5484"},{"$f":"SUBSTITUTE(P9,Q$3,Q$4)","v":"Po Box 5484"},"Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","Po Box 5484","PO Box 5484","PO Box 5484","PO Box 5484"]
["P. O. Box 10984","PO Box 10984",null,"P. O. Box 10984",{"$f":"SUBSTITUTE(D10,E$3,E$4)","v":"P. O. Box 10984"},"P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984",null,{"$f":"SUBSTITUTE(N10,P$3,P$4)","v":"P. O. Box 10984"},{"$f":"SUBSTITUTE(P10,Q$3,Q$4)","v":"P. O. Box 10984"},"P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","P. O. Box 10984","PO Box 10984","PO Box 10984","PO Box 10984","PO Box 10984"]
["Po Box 10984","PO Box 10984",null,"Po Box 10984",{"$f":"SUBSTITUTE(D11,E$3,E$4)","v":"Po Box 10984"},"Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984",null,{"$f":"SUBSTITUTE(N11,P$3,P$4)","v":"Po Box 10984"},{"$f":"SUBSTITUTE(P11,Q$3,Q$4)","v":"Po Box 10984"},"Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","Po Box 10984","PO Box 10984","PO Box 10984","PO Box 10984"]
[]
[]
["Insert Columns to Left"]
["Cut/Paste Addresses in to new columns"]
["Copy/Past Address Column to be cleaned into \"Input Column\""]
["Copy/Paste Additional Rows as Necessary (starting from current \"B\" Column\" to far right column) to match the number of addresses/rows"]
["Cut / \"Paste as Values\" the results from the \"B\" column into your original address column"]