{"id":284,"date":"2012-02-10T19:59:10","date_gmt":"2012-02-11T01:59:10","guid":{"rendered":"http:\/\/xlfxs.com\/?p=284"},"modified":"2012-02-10T19:59:10","modified_gmt":"2012-02-11T01:59:10","slug":"extract-address-into-table","status":"publish","type":"post","link":"https:\/\/xlfxs.com\/?p=284","title":{"rendered":"Extract Address into table"},"content":{"rendered":"

Convert US addresses from cells (one column with 1 line for Name, 1 line for address, one line for City, State and Zip code) into table<\/p>\n

Now, say you have multiple addresses in column B, structured as line per cell, like screenshot above<\/p>\n

Which is usually what you got from any list of addresses online, and you want to convert it to table.<\/p>\n

This is exactly what one of clients had and needed, so here is the file that does that.<\/p>\n

Basically you need to have 5 columns with formula in column G as:<\/p>\n

=OFFSET($B$1,(ROW()-5)*4+4,0)<\/pre>\n
to extract first line (Starting from cell B5<\/p>\n
Then in column H as:<\/p>\n
<\/p>\n
=OFFSET($B$1,(ROW()-5)*4+5,0)<\/pre>\n
Now, in column I you need to extract only city name, so you need this formula:<\/p>\n
=LEFT(OFFSET($B$1,(ROW()-5)*4+6,0),SEARCH(\",\",OFFSET($B$1,(ROW()-5)*4+6,0))-1)<\/pre>\n
Then in J, use this formula:<\/p>\n
=MID(OFFSET($B$1,(ROW()-5)*4+6,0),SEARCH(\",\",OFFSET($B$1,(ROW()-5)*4+6,0))+2,2)<\/pre>\n
To get state, then in K this:<\/p>\n
=MID(OFFSET($B$1,(ROW()-5)*4+6,0),SEARCH(J5,OFFSET($B$1,(ROW()-5)*4+6,0))+3,5)<\/pre>\n
to get the Zipcode:<\/p>\n
 <\/p>\n
This way, you will convert all addresses into a table with address per row.<\/p>\n
\"\"<\/a><\/p>\n\n","protected":false},"excerpt":{"rendered":"
Convert US addresses from cells (one column with 1 line for Name, 1 line for address, one line for City, State and Zip code) into table Now, say you have multiple addresses in column B, structured as line per cell, like screenshot above Which is usually what you got from any list of addresses online,…<\/p>\n
Read More “Extract Address into table”<\/span> »<\/a><\/p>\n","protected":false},"author":1,"featured_media":287,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,33,36,40,47,51,52,6,8],"tags":[],"class_list":["post-284","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-formulas","category-left","category-lookup_and_references","category-mid","category-offset","category-row","category-search","category-standard_functions","category-texts-and-strings"],"_links":{"self":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/284","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=284"}],"version-history":[{"count":0,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/284\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/media\/287"}],"wp:attachment":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}