Need to convert/transpose single column of addresses to separate columns (name, city, state, etc.), but not all addresses have the same number of rows

Myhobohemia

Board Regular
Joined
Mar 12, 2020
Messages
54
Office Version
  1. 365
Platform
  1. Windows
HI there. As I said in the subject line, I need to convert a single column of addresses and transpose them into their separate fields. This would be fairly easy, except that the number of rows is irregular, ranging from 6-13 rows. Most are about 7 or 8. I can't figure out how to automate this. Right now, the individual addresses in the column are not separated by anything. The last row of each address is a hyperlink, for what it's worth (and hyperlinks are searchable using the search feature, I learned yesterday, but to no avail). I have never done a macro or used VBA in my life! :oops: I can, however, follow clear instructions. ? I have dozens of separate documents, some with hundreds of addresses, some with only a handful. The worstcase scenario would be to have to transpose each address manually. But I'd like to avoid it, particularly since it looks like there will still be plenty of manual work later, getting everything into the right column. Any suggestions?! Thanks in advance. Attaching an image of the data.
 

Attachments

  • 20.03.12  Address data that needs to be transposed.JPG
    20.03.12 Address data that needs to be transposed.JPG
    54.2 KB · Views: 48

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Myhobohemia

Board Regular
Joined
Mar 12, 2020
Messages
54
Office Version
  1. 365
Platform
  1. Windows
Hello there. This was going great but ran into a hiccup. I can't get the Colorado sheet to work properly.... it won't find hardly any zips using the formulas. It did, however, find the last zip. ?

Where am I going wrong here? This is in a clean Excel with no modules or macros.


COLORADO.xlsx
ABCDEFGH
1Order No.Practice NameStreet AddressAddress line 2CityStateJSONZip
21Memorial Hospital Central- University of Colorado Health 1400 East Boulder Colorado SpringsCO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
32South Denver Heart Center 1000 South Park Drive Littleton CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
43SCL Health Medical Group - Denver, LLC 1960 N. Ogden Street Suite 510 Denver CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
54Cardiovascular Institute of North Colorado 1800 15th Street Suite 310 Greeley CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
65Colorado Heart and Vascular, P.C. - North 14300 Orchard Parkway Westminster CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
76SCL Health Medical Group - Denver, LLC 3655 Lutheran Parkway Suite 201 Wheat Ridge CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
87Colorado Heart and Vascular, P.C. - Central 11700 West 2nd Place Suite 350 Lakewood CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
98SCL Health Medical Group - Denver, LLC 300 Exempla Circle Suite 380 Lafayette CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
109Colorado Heart and Vascular, PC 90 Health Park Drive Suite 190 Louisville CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
1110Colorado Heart and Vascular, PC 2030 Mountian View Avenue Suite 300 Longmont CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
1211Boulder Heart 2101 Ken Pratt Boulevard Longmont CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
1312University of Colorado Health-Medical Center of the Rockies 2500 Rocky Mountain Ave Suite 1313 Loveland CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
1413Boulder Heart 4743 Arapahoe Avenue Suite 201 Boulder CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
1514University of Colorado Health-Poudre Valley Hospital-Harmony Campus 2121 E. Harmony Road Suite 200 Fort Collins CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
1615University of Colorado Health - Boulder Cadiovascular 2750 Broadway Boulder CO {"addresses":[], "formExcepts": "", "serverErrorString": "The address you entered wasn't found.<br/> Please double-check it and try again.<br/>" } #VALUE!
1716Western Slope Cardiology, PC 2643 Patterson Road Suite 505 Grand Junction CO{ "addresses": [ { "co" : "", "address1" : "2643 PATTERSON RD", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "81506-1936" }, { "co" : "", "address1" : "(ODD Range 2601 - 2643) PATTERSON RD", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1940" }, { "co" : "", "address1" : "2643 PATTERSON RD STE (Range 200 - 406)", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1937" }, { "co" : "", "address1" : "2643 PATTERSON RD STE (Range 501 - 605)", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1937" }, { "co" : "", "address1" : "2643 PATTERSON RD STE 425", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1942" }, { "co" : "", "address1" : "2643 PATTERSON RD STE 450", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1949" }, { "co" : "", "address1" : "2643 PATTERSON RD STE 475", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1965" }]} 81506
Sheet1
Cell Formulas
RangeFormula
G2:G17G2=IF(AND(C2<>"",E2<>"",F2<>""),WEBSERVICE("https://m.usps.com/m/QuickZipAction?mode=0&tAddress=" & SUBSTITUTE(C2," ","+") & "&tApt=&tCity=" & E2 & "&sState=" & F2 & "&jsonInd=Y"),"NA")
H2:H17H2=MID(G2,FIND("""zip""",G2)+9,5)
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,555
Office Version
  1. 365
Platform
  1. Windows
I have no idea. Maybe a weird internet problem or something. I literally copied what you posted in your last post, and everything worked as it should. Here are the results of me just copy pasting from your last post.

Book1
ABCDEFGH
1Order No.Practice NameStreet AddressAddress line 2CityStateJSONZip
21Memorial Hospital Central- University of Colorado Health1400 East BoulderColorado SpringsCO{ "addresses": [ { "co" : "", "address1" : "1400 E BOULDER ST", "address2" : "", "city" : "COLORADO SPRINGS", "cityStatus" : "", "state" : "CO", "zipStatus" : "S", "zip" : "80909-5533" }]} 80909
32South Denver Heart Center1000 South Park DriveLittletonCO{ "addresses": [ { "co" : "", "address1" : "1000 SOUTHPARK DR", "address2" : "", "city" : "LITTLETON", "cityStatus" : "", "state" : "CO", "zipStatus" : "S", "zip" : "80120-5654" }]} 80120
43SCL Health Medical Group - Denver, LLC1960 N. Ogden StreetSuite 510DenverCO{ "addresses": [ { "co" : "", "address1" : "1960 N OGDEN ST", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "80218-3666" }, { "co" : "", "address1" : "1960 N OGDEN ST STE 555", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3602" }, { "co" : "", "address1" : "1960 N OGDEN ST STE 260", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3650" }, { "co" : "", "address1" : "1960 N OGDEN ST STE 130", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3662" }, { "co" : "", "address1" : "1960 N OGDEN ST STE 200", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3663" }, { "co" : "", "address1" : "1960 N OGDEN ST STE 280", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3664" }, { "co" : "", "address1" : "1960 N OGDEN ST STE (EVEN Range 10 - 120)", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3667" }, { "co" : "", "address1" : "1960 N OGDEN ST STE (EVEN Range 210 - 250)", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3668" }, { "co" : "", "address1" : "1960 N OGDEN ST STE (EVEN Range 320 - 360)", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3669" }, { "co" : "", "address1" : "1960 N OGDEN ST STE (EVEN Range 400 - 490)", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3670" }, { "co" : "", "address1" : "1960 N OGDEN ST STE (EVEN Range 500 - 520)", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3671" }, { "co" : "", "address1" : "1960 N OGDEN ST STE (EVEN Range 530 - 540)", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3671" }, { "co" : "", "address1" : "1960 N OGDEN ST STE (Range 560 - 580)", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3672" }, { "co" : "", "address1" : "1960 N OGDEN ST STE (EVEN Range 620 - 690)", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3673" }, { "co" : "", "address1" : "1960 N OGDEN ST STE (EVEN Range 300 - 310)", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3674" }, { "co" : "", "address1" : "1960 N OGDEN ST STE 525", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3675" }, { "co" : "", "address1" : "1960 N OGDEN ST STE 550", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3676" }, { "co" : "", "address1" : "1960 N OGDEN ST STE 590", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-3677" }, { "co" : "", "address1" : "(EVEN Range 1900 - 1998) N OGDEN ST", "address2" : "", "city" : "DENVER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80218-1022" }]} 80218
54Cardiovascular Institute of North Colorado1800 15th StreetSuite 310GreeleyCO{ "addresses": [ { "co" : "", "address1" : "1800 15TH ST", "address2" : "", "city" : "GREELEY", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "80631-4500" }, { "co" : "", "address1" : "1800 15TH ST STE (EVEN Range 300 - 340)", "address2" : "", "city" : "GREELEY", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80631-4562" }, { "co" : "", "address1" : "1800 15TH ST STE (EVEN Range 200 - 220)", "address2" : "", "city" : "GREELEY", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80631-4563" }, { "co" : "", "address1" : "1800 15TH ST STE (Range A - D)", "address2" : "", "city" : "GREELEY", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80631-4595" }, { "co" : "", "address1" : "1800 15TH ST STE (EVEN Range 100 - 130)", "address2" : "", "city" : "GREELEY", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80631-4595" }, { "co" : "", "address1" : "1800 15TH ST STE 100-B", "address2" : "", "city" : "GREELEY", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80631-4595" }, { "co" : "", "address1" : "1800 15TH ST STE G10", "address2" : "", "city" : "GREELEY", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80631-4595" }, { "co" : "", "address1" : "1800 15TH ST STE 380", "address2" : "", "city" : "GREELEY", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80631-4596" }, { "co" : "", "address1" : "(EVEN Range 1800 - 1898) 15TH ST", "address2" : "", "city" : "GREELEY", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80631-4572" }]} 80631
65Colorado Heart and Vascular, P.C. - North14300 Orchard ParkwayWestminsterCO{ "addresses": [ { "co" : "", "address1" : "14300 ORCHARD PKWY", "address2" : "", "city" : "WESTMINSTER", "cityStatus" : "", "state" : "CO", "zipStatus" : "S", "zip" : "80023-9206" }]} 80023
76SCL Health Medical Group - Denver, LLC3655 Lutheran ParkwaySuite 201Wheat RidgeCO{ "addresses": [ { "co" : "", "address1" : "3655 LUTHERAN PKWY", "address2" : "", "city" : "WHEAT RIDGE", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "80033-6018" }, { "co" : "", "address1" : "3655 LUTHERAN PKWY STE (Range 101 - 110)", "address2" : "", "city" : "WHEAT RIDGE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80033-6009" }, { "co" : "", "address1" : "3655 LUTHERAN PKWY STE 201", "address2" : "", "city" : "WHEAT RIDGE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80033-6010" }, { "co" : "", "address1" : "3655 LUTHERAN PKWY STE (Range 300 - 308)", "address2" : "", "city" : "WHEAT RIDGE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80033-6011" }, { "co" : "", "address1" : "3655 LUTHERAN PKWY STE (Range 401 - 409)", "address2" : "", "city" : "WHEAT RIDGE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80033-6012" }, { "co" : "", "address1" : "3655 LUTHERAN PKWY STE 100", "address2" : "", "city" : "WHEAT RIDGE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80033-6020" }, { "co" : "RICHARD V GANDER DDS PC", "address1" : "3655 LUTHERAN PKWY STE 402", "address2" : "", "city" : "WHEAT RIDGE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80033-6061" }, { "co" : "", "address1" : "(ODD Range 3601 - 3699) LUTHERAN PKWY", "address2" : "", "city" : "WHEAT RIDGE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80033-6015" }]} 80033
87Colorado Heart and Vascular, P.C. - Central11700 West 2nd PlaceSuite 350LakewoodCO{ "addresses": [ { "co" : "", "address1" : "11700 W 2ND PL", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "80228-1704" }, { "co" : "", "address1" : "11700 W 2ND PL STE (EVEN Range 100 - 140)", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1707" }, { "co" : "", "address1" : "11700 W 2ND PL STE (ODD Range 225 - 265)", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1707" }, { "co" : "", "address1" : "11700 W 2ND PL STE (Range 305 - 310)", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1710" }, { "co" : "", "address1" : "11700 W 2ND PL STE (Range 345 - 350)", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1710" }, { "co" : "", "address1" : "11700 W 2ND PL STE 465", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1711" }, { "co" : "", "address1" : "11700 W 2ND PL STE (Range 405 - 425)", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1711" }, { "co" : "", "address1" : "11700 W 2ND PL STE 445", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1711" }, { "co" : "", "address1" : "11700 W 2ND PL STE 150", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1715" }, { "co" : "", "address1" : "11700 W 2ND PL STE 210", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1716" }, { "co" : "", "address1" : "11700 W 2ND PL STE 280", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1717" }, { "co" : "", "address1" : "11700 W 2ND PL STE 380", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1718" }, { "co" : "", "address1" : "11700 W 2ND PL STE 450", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1719" }, { "co" : "", "address1" : "11700 W 2ND PL STE 435", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1732" }, { "co" : "", "address1" : "11700 W 2ND PL STE 325", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1755" }, { "co" : "", "address1" : "(EVEN Range 11700 - 11798) W 2ND PL", "address2" : "", "city" : "LAKEWOOD", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80228-1573" }]} 80228
98SCL Health Medical Group - Denver, LLC300 Exempla CircleSuite 380LafayetteCO{ "addresses": [ { "co" : "", "address1" : "300 EXEMPLA CIR", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "80026-3397" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE 210", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-2905" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE (EVEN Range 230 - 240)", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-2906" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE 365", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-2908" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE (EVEN Range 110 - 120)", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3389" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE 170", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3390" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE 200", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3391" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE 250", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3392" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE 270", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3393" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE (EVEN Range 300 - 310)", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3394" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE 360", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3395" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE (EVEN Range 370 - 380)", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3395" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE 400", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3396" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE (EVEN Range 460 - 470)", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3396" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE 130", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3484" }, { "co" : "", "address1" : "300 EXEMPLA CIR STE 420", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3496" }, { "co" : "", "address1" : "(EVEN Range 300 - 398) EXEMPLA CIR", "address2" : "", "city" : "LAFAYETTE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80026-3384" }]} 80026
109Colorado Heart and Vascular, PC90 Health Park DriveSuite 190LouisvilleCO{ "addresses": [ { "co" : "", "address1" : "90 HEALTH PARK DR", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "80027-9757" }, { "co" : "", "address1" : "90 HEALTH PARK DR STE 260", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-4521" }, { "co" : "", "address1" : "90 HEALTH PARK DR STE 170", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-8702" }, { "co" : "", "address1" : "90 HEALTH PARK DR STE (EVEN Range 100 - 130)", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9586" }, { "co" : "", "address1" : "90 HEALTH PARK DR STE (EVEN Range 190 - 220)", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9586" }, { "co" : "", "address1" : "90 HEALTH PARK DR STE 290", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9586" }, { "co" : "MEDICAL BLDG", "address1" : "90 HEALTH PARK DR STE 300", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9586" }, { "co" : "", "address1" : "90 HEALTH PARK DR STE 310", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9586" }, { "co" : "", "address1" : "90 HEALTH PARK DR STE 330", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9586" }, { "co" : "MEDICAL BLDG", "address1" : "90 HEALTH PARK DR STE 340", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9586" }, { "co" : "MEDICAL BLDG", "address1" : "90 HEALTH PARK DR STE 370", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9586" }, { "co" : "", "address1" : "90 HEALTH PARK DR STE (EVEN Range 140 - 160)", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9742" }, { "co" : "", "address1" : "90 HEALTH PARK DR STE 320", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9742" }, { "co" : "MEDICAL BLDG", "address1" : "90 HEALTH PARK DR STE 350", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9742" }, { "co" : "MEDICAL BLDG", "address1" : "90 HEALTH PARK DR STE 390", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9742" }, { "co" : "", "address1" : "90 HEALTH PARK DR STE 240", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9757" }, { "co" : "", "address1" : "(EVEN Range 2 - 198) HEALTH PARK DR", "address2" : "", "city" : "LOUISVILLE", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80027-9583" }]} 80027
1110Colorado Heart and Vascular, PC2030 Mountian View AvenueSuite 300LongmontCO{ "addresses": [ { "co" : "", "address1" : "2030 MOUNTAIN VIEW AVE", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "80501-3178" }, { "co" : "", "address1" : "2030 MOUNTAIN VIEW AVE STE (EVEN Range 350 - 370)", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-3103" }, { "co" : "", "address1" : "2030 MOUNTAIN VIEW AVE STE (EVEN Range 100 - 110)", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-3179" }, { "co" : "", "address1" : "2030 MOUNTAIN VIEW AVE STE (EVEN Range 200 - 230)", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-3180" }, { "co" : "", "address1" : "2030 MOUNTAIN VIEW AVE STE (EVEN Range 300 - 330)", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-3181" }, { "co" : "", "address1" : "2030 MOUNTAIN VIEW AVE STE (EVEN Range 400 - 440)", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-3182" }, { "co" : "", "address1" : "2030 MOUNTAIN VIEW AVE STE (EVEN Range 500 - 560)", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-3183" }, { "co" : "", "address1" : "2030 MOUNTAIN VIEW AVE STE 250", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-3273" }, { "co" : "", "address1" : "(EVEN Range 2000 - 2098) MOUNTAIN VIEW AVE", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-3131" }]} 80501
1211Boulder Heart2101 Ken Pratt BoulevardLongmontCO{ "addresses": [ { "co" : "", "address1" : "2101 KEN PRATT BLVD", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "80501-6567" }, { "co" : "", "address1" : "2101 KEN PRATT BLVD STE 102", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-6085" }, { "co" : "", "address1" : "2101 KEN PRATT BLVD STE (Range 201 - 204)", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-6085" }, { "co" : "", "address1" : "2101 KEN PRATT BLVD STE (Range 104 - 200)", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-6568" }, { "co" : "", "address1" : "2101 KEN PRATT BLVD STE (Range 100 - 101)", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-6568" }, { "co" : "", "address1" : "(ODD Range 2101 - 2199) KEN PRATT BLVD", "address2" : "", "city" : "LONGMONT", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80501-6595" }]} 80501
1312University of Colorado Health-Medical Center of the Rockies2500 Rocky Mountain AveSuite 1313LovelandCO{ "addresses": [ { "co" : "", "address1" : "2500 ROCKY MOUNTAIN AVE", "address2" : "", "city" : "LOVELAND", "cityStatus" : "", "state" : "CO", "zipStatus" : "S", "zip" : "80538-9004" }]} 80538
1413Boulder Heart4743 Arapahoe AvenueSuite 201BoulderCO{ "addresses": [ { "co" : "", "address1" : "4743 ARAPAHOE AVE", "address2" : "", "city" : "BOULDER", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "80303-1113" }, { "co" : "", "address1" : "4743 ARAPAHOE AVE STE (Range 100 - 104)", "address2" : "", "city" : "BOULDER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80303-1123" }, { "co" : "", "address1" : "4743 ARAPAHOE AVE STE (Range 200 - 202)", "address2" : "", "city" : "BOULDER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80303-1128" }, { "co" : "", "address1" : "(ODD Range 4701 - 4799) ARAPAHOE AVE", "address2" : "", "city" : "BOULDER", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80303-1133" }]} 80303
1514University of Colorado Health-Poudre Valley Hospital-Harmony Campus2121 E. Harmony RoadSuite 200Fort CollinsCO{ "addresses": [ { "co" : "", "address1" : "2121 E HARMONY RD", "address2" : "", "city" : "FORT COLLINS", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "80528-3400" }, { "co" : "", "address1" : "2121 E HARMONY RD UNIT (EVEN Range 100 - 120)", "address2" : "", "city" : "FORT COLLINS", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80528-3401" }, { "co" : "", "address1" : "2121 E HARMONY RD UNIT (EVEN Range 180 - 230)", "address2" : "", "city" : "FORT COLLINS", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80528-3401" }, { "co" : "", "address1" : "2121 E HARMONY RD UNIT 135", "address2" : "", "city" : "FORT COLLINS", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80528-3401" }, { "co" : "", "address1" : "2121 E HARMONY RD UNIT (EVEN Range 250 - 290)", "address2" : "", "city" : "FORT COLLINS", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80528-3402" }, { "co" : "", "address1" : "2121 E HARMONY RD UNIT (EVEN Range 300 - 330)", "address2" : "", "city" : "FORT COLLINS", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80528-3403" }, { "co" : "", "address1" : "2121 E HARMONY RD UNIT (Range 350 - 380)", "address2" : "", "city" : "FORT COLLINS", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80528-3404" }, { "co" : "", "address1" : "2121 E HARMONY RD UNIT (EVEN Range 150 - 170)", "address2" : "", "city" : "FORT COLLINS", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80528-3413" }, { "co" : "", "address1" : "2121 E HARMONY RD UNIT 125", "address2" : "", "city" : "FORT COLLINS", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80528-3416" }, { "co" : "", "address1" : "(ODD Range 2101 - 2199) E HARMONY RD", "address2" : "", "city" : "FORT COLLINS", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "80528-9503" }]} 80528
1615University of Colorado Health - Boulder Cadiovascular2750 BroadwayBoulderCO{ "addresses": [ { "co" : "", "address1" : "2750 BROADWAY ST", "address2" : "", "city" : "BOULDER", "cityStatus" : "", "state" : "CO", "zipStatus" : "S", "zip" : "80304-3573" }]} 80304
1716Western Slope Cardiology, PC2643 Patterson RoadSuite 505Grand JunctionCO{ "addresses": [ { "co" : "", "address1" : "2643 PATTERSON RD", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "H", "zip" : "81506-1936" }, { "co" : "", "address1" : "(ODD Range 2601 - 2643) PATTERSON RD", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1940" }, { "co" : "", "address1" : "2643 PATTERSON RD STE (Range 200 - 406)", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1937" }, { "co" : "", "address1" : "2643 PATTERSON RD STE (Range 501 - 605)", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1937" }, { "co" : "", "address1" : "2643 PATTERSON RD STE 425", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1942" }, { "co" : "", "address1" : "2643 PATTERSON RD STE 450", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1949" }, { "co" : "", "address1" : "2643 PATTERSON RD STE 475", "address2" : "", "city" : "GRAND JUNCTION", "cityStatus" : "", "state" : "CO", "zipStatus" : "", "zip" : "81506-1965" }]} 81506
Sheet1
Cell Formulas
RangeFormula
G2:G17G2=IF(AND(C2<>"",E2<>"",F2<>""),WEBSERVICE("https://m.usps.com/m/QuickZipAction?mode=0&tAddress=" & SUBSTITUTE(C2," ","+") & "&tApt=&tCity=" & E2 & "&sState=" & F2 & "&jsonInd=Y"),"NA")
H2:H17H2=MID(G2,FIND("""zip""",G2)+9,5)
 

Myhobohemia

Board Regular
Joined
Mar 12, 2020
Messages
54
Office Version
  1. 365
Platform
  1. Windows
Oh wow! Well that's good news ... at least I can get it that way. Thanks! (y)
 

Forum statistics

Threads
1,175,824
Messages
5,899,686
Members
434,795
Latest member
tracid1987

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top