{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["StateList","Question","Start","Bill","Mike","Sheet3"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":1,"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":"StateList","index":0,"n_rows":52,"n_cols":13,"rows_kept":52,"cut":false,"hidden":false,"merged":[]}
["State","StateFull","Census Region","http://www.eia.doe.gov/emeu/reps/maps/us_census.html",null,null,null,null,null,null,"652 Pelzer Drive, Mt Pleasant, SC 29464",{"$f":"SEARCH(\"SC\",K1,1)","v":32},{"$f":"MID(K1,SEARCH(\"SC\",K1,1),2)","v":"SC"}]
["AK","Alaska","West Pacific"]
["AL","Alabama","East South Central"]
["AR","Arkansas","West South Central"]
["AZ","Arizona","West Mountain"]
["CA","California","West Pacific"]
["CO","Colorado","West Mountain"]
["CT","Connecticut","New England"]
["DC","Dist of Columbia","South Atlantic"]
["DE","Delaware","South Atlantic"]
["FL","Florida","South Atlantic"]
["GA","Georgia","South Atlantic"]
["HI","Hawaii","West Pacific"]
["IA","Ioaw","West North Central"]
["ID","Idaho","West Mountain"]
["IL","Illinois","East North Central"]
["IN","Indiana","East North Central"]
["KS","Kansas","West North Central"]
["KY","Kentucky","East South Central"]
["LA","Lousiana","West South Central"]
["MA","Massachusetts","New England"]
["MD","Maryland","South Atlantic"]
["ME","Maine","New England"]
["MI","Michigan","East North Central"]
["MN","Minnesota","West North Central"]
["MO","Missouri","West North Central"]
["MS","Mississippi","East South Central"]
["MT","Montana","West Mountain"]
["NC","North Carolina","South Atlantic"]
["ND","North Dakota","West North Central"]
["NE","Nebraska","West North Central"]
["NH","New Hampshire","New England"]
["NJ","New Jersey","Middle Atlantic"]
["NM","New Mexico","West Mountain"]
["NV","Nevada","West Mountain"]
["NY","New York","Middle Atlantic"]
["OH","Ohio","East North Central"]
["OK","Oklahoma","West South Central"]
["OR","Oregan","West Pacific"]
["PA","Pennsylvania","Middle Atlantic"]
["RI","Rhode Island","New England"]
["SC","South Carolina","South Atlantic"]
["SD","South Dakota","West North Central"]
["TN","Tennessee","East South Central"]
["TX","Texas","West South Central"]
["UT","Utah","West Mountain"]
["VA","Virginia","South Atlantic"]
["VT","Vermont","New England"]
["WA","Washington","West Pacific"]
["WI","Wisconsin","East North Central"]
["WV","West Virginia","South Atlantic"]
["WY","Wyoming","West Mountain"]

{"sheet":"Question","index":1,"n_rows":21,"n_cols":5,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
["Extract just the state from a cell problem"]
[null,"=MID function idea",null,"=countif function"]
["Address","State",null,null,"correct answer"]
["1234 Fifth Street, Charleston, SC 29401-2367",{"$f":"MID(A4,FIND(\", \",A4,FIND(\", \",A4)+1)+2,2)","v":"SC"},null,{"$f":"COUNTIF(A4,\"*, *\")","v":1},2]
["750 Main Street, Chicago, IL 60601",{"$f":"MID(A5,FIND(\", \",A5,FIND(\", \",A5)+1)+2,2)","v":"IL"},null,{"$f":"COUNTIF(A5,\"*, *\")","v":1},2]
["358A Center Blvd, Las Vegas, NV 89045","NV",null,1,2]
["1 Wall Street, New York City, NY 10001","NY",null,1,2]
["497 Columbia Street, Apt 9C, Charleston, SC 29434","Ch","error - pulled first two of city not state",1,3]
[]
[]
["Challenge"]
["Extract just the state from the cell"]
["I tried the =Find function but it looks for a specific string and matching from a range from the Regions tab doesn’t work."]
["I could nest 50 \"if\" statements, 1 for each state, but it seems like a long formula with lots of potential for errors & a lot of calculations for 185000 records"]
["I tried using the Mid function to locate the second \", \" text and then apply the mid formula until I had an address which had three \", \"'s in the address; see above"]
["I thought about trying to count the number of \", \"'s in the string but couldn’t find a formula which would work"]
["After insterting a \"*\" before and after the string I was able to get the count to 1 but not 2 or 3 which it should be; appears to stop at first occurance. "]
["If I could determine the number of occurances I could write an =If fomula for \"x\" occurances to generate the value needed"]
["I am not sure how to change this formula to find the last \", \" in the string, suggestions?"]
["Another option would be to locate the state abbreviation in the cell from a range of state abbreviations on another sheet, not sure what formula would do this"]
["I could use text to column but it would take several itterations to extract and on a table with 185,000 lines its not practical. "]

{"sheet":"Start","index":2,"n_rows":8,"n_cols":3,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Address",null,"Just State"]
["1234 Akton Street, Charleston, SC 29401-2367"]
["750 Main Street, Chicago, IL 60601"]
["358A Center Blvd, Las Vegas, NV 89045"]
["1 Wall Street, New York City, NY 10001"]
["497 Columbia Street, Apt 9C, Charleston, SC 29434"]
["6943 1st St, Building 12, Room 43, Radtown, CA 94600"]
["Oakland, CA 94618"]

{"sheet":"Bill","index":3,"n_rows":8,"n_cols":3,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Address",null,"Just State"]
["1234 Fifth Street, Charleston, SC 29401-2367",null,{"$f":"GetState(A2)","v":"SC"}]
["750 Main Street, Chicago, IL 60601",null,{"$f":"GetState(A3)","v":"IL"}]
["358A Center Blvd, Las Vegas, NV 89045",null,"NV"]
["1 Wall Street, New York City, NY 10001",null,"NY"]
["497 Columbia Street, Apt 9C, Charleston, SC 29434",null,"SC"]
["6943 1st St, Building 12, Room 43, Radtown, CA 94600",null,"CA"]
["Oakland, CA 94618",null,"CA"]

{"sheet":"Mike","index":4,"n_rows":18,"n_cols":3,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["Address",null,"Just State"]
["1234 Akton Street, Charleston, SC 29401-2367",null,{"$f":"MID(A2,MATCH(2,INDEX(1/(MID(A2,ROW(INDIRECT(\"1:\"&LEN(A2))),1)=\",\"),))+2,2)","v":"SC"}]
["750 Main Street, Chicago, IL 60601",null,{"$f":"MID(A3,MATCH(2,INDEX(1/(MID(A3,ROW(INDIRECT(\"1:\"&LEN(A3))),1)=\",\"),))+2,2)","v":"IL"}]
["358A Center Blvd, Las Vegas, NV 89045",null,"NV"]
["1 Wall Street, New York City, NY 10001",null,"NY"]
["497 Columbia Street, Apt 9C, Charleston, SC 29434",null,"SC"]
["6943 1st St, Building 12, Room 43, Radtown, CA 94600",null,"CA"]
["Oakland, CA 94618",null,"CA"]
[]
["Extra Space Trouble"]
["Address",null,"Just State"]
["1234 Fifth Street, Charleston,  SC 29401-2367",null,{"$f":"TRIM(MID(TRIM(A12),MATCH(2,1/(MID(TRIM(A12),ROW(INDIRECT(\"1:\"&LEN(TRIM(A12)))),1)=\",\"))+1,3))","v":"SC"}]
["750 Main Street, Chicago, IL 60601",null,{"$f":"TRIM(MID(TRIM(A13),MATCH(2,1/(MID(TRIM(A13),ROW(INDIRECT(\"1:\"&LEN(TRIM(A13)))),1)=\",\"))+1,3))","v":"IL"}]
["358A Center Blvd, Las Vegas, NV 89045",null,{"$f":"TRIM(MID(TRIM(A14),MATCH(2,1/(MID(TRIM(A14),ROW(INDIRECT(\"1:\"&LEN(TRIM(A14)))),1)=\",\"))+1,3))","v":"NV"}]
["1 Wall Street, New York City,NY 10001",null,{"$f":"TRIM(MID(TRIM(A15),MATCH(2,1/(MID(TRIM(A15),ROW(INDIRECT(\"1:\"&LEN(TRIM(A15)))),1)=\",\"))+1,3))","v":"NY"}]
["497 Columbia Street, Apt 9C, Charleston, SC 29434",null,{"$f":"TRIM(MID(TRIM(A16),MATCH(2,1/(MID(TRIM(A16),ROW(INDIRECT(\"1:\"&LEN(TRIM(A16)))),1)=\",\"))+1,3))","v":"SC"}]
["6943 1st St, Building 12, Room 43, Radtown,     CA 94600",null,{"$f":"TRIM(MID(TRIM(A17),MATCH(2,1/(MID(TRIM(A17),ROW(INDIRECT(\"1:\"&LEN(TRIM(A17)))),1)=\",\"))+1,3))","v":"CA"}]
["Oakland, CA 94618",null,{"$f":"TRIM(MID(TRIM(A18),MATCH(2,1/(MID(TRIM(A18),ROW(INDIRECT(\"1:\"&LEN(TRIM(A18)))),1)=\",\"))+1,3))","v":"CA"}]

{"sheet":"Sheet3","index":5,"n_rows":18,"n_cols":5,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["Address",null,"Just State",null,"Because could be duplicates"]
["1234 Fifth Street, Charleston, SC 29401-2367",null,{"$f":"MID(A2,MATCH(2,INDEX(1/(MID(A2,ROW(INDIRECT(\"1:\"&LEN(A2))),1)=\",\"),))+2,2)","v":"SC"},null,{"$f":"SEARCH(State,A2)","v":{"$error":"#VALUE!"}}]
["750 Main Street, Chicago, IL 60601",null,{"$f":"MID(A3,MATCH(2,INDEX(1/(MID(A3,ROW(INDIRECT(\"1:\"&LEN(A3))),1)=\",\"),))+2,2)","v":"IL"}]
["358A Center Blvd, Las Vegas, NV 89045",null,"NV"]
["1 Wall Street, New York City, NY 10001",null,"NY"]
["497 Columbia Street, Apt 9C, Charleston, SC 29434",null,"SC"]
["6943 1st St, Building 12, Room 43, Radtown, CA 94600",null,"CA"]
["Oakland, CA 94618",null,"CA"]
[]
["Extra Space Trouble"]
["Address",null,"Just State"]
["1234 Fifth Street, Charleston,  SC 29401-2367",null,{"$f":"TRIM(MID(TRIM(A12),MATCH(2,1/(MID(TRIM(A12),ROW(INDIRECT(\"1:\"&LEN(TRIM(A12)))),1)=\",\"))+1,3))","v":"SC"}]
["750 Main Street, Chicago, IL 60601",null,{"$f":"TRIM(MID(TRIM(A13),MATCH(2,1/(MID(TRIM(A13),ROW(INDIRECT(\"1:\"&LEN(TRIM(A13)))),1)=\",\"))+1,3))","v":"IL"}]
["358A Center Blvd, Las Vegas, NV 89045",null,{"$f":"TRIM(MID(TRIM(A14),MATCH(2,1/(MID(TRIM(A14),ROW(INDIRECT(\"1:\"&LEN(TRIM(A14)))),1)=\",\"))+1,3))","v":"NV"}]
["1 Wall Street, New York City,NY 10001",null,{"$f":"TRIM(MID(TRIM(A15),MATCH(2,1/(MID(TRIM(A15),ROW(INDIRECT(\"1:\"&LEN(TRIM(A15)))),1)=\",\"))+1,3))","v":"NY"}]
["497 Columbia Street, Apt 9C, Charleston, SC 29434",null,{"$f":"TRIM(MID(TRIM(A16),MATCH(2,1/(MID(TRIM(A16),ROW(INDIRECT(\"1:\"&LEN(TRIM(A16)))),1)=\",\"))+1,3))","v":"SC"}]
["6943 1st St, Building 12, Room 43, Radtown,     CA 94600",null,{"$f":"TRIM(MID(TRIM(A17),MATCH(2,1/(MID(TRIM(A17),ROW(INDIRECT(\"1:\"&LEN(TRIM(A17)))),1)=\",\"))+1,3))","v":"CA"}]
["Oakland, CA 94618",null,{"$f":"TRIM(MID(TRIM(A18),MATCH(2,1/(MID(TRIM(A18),ROW(INDIRECT(\"1:\"&LEN(TRIM(A18)))),1)=\",\"))+1,3))","v":"CA"}]