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]

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! 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.

 

[lrobbo314]

I think that you can, but you need to have the latest Office 365 Insider Edition to access those functions.

Get geographic location data

 

[Myhobohemia]

Thank you! I'm going to check that out and report back.

 

[Fluff]

Hello! Sorry for delay in responding. It's great! Worked like a charm. Thank you!

You're welcome & thanks for the feedback.

I've no idea how to get the Zip codes, so cannot help on that.

 

[lrobbo314]

Here is a way to do it. I just went to USPS.com to lookup the zip code from the address and used that info in a http get request and parsed out the result with some regex.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Order	Practice Name	Street Address	Address line 2	City	State	Zip Code	Web link	Accred1	Accred1 Date
2	1	Selma Family Care, PC	203 Vaughan Memorial Drive		Selma	AL	36701	Selma Family Care, PC	Nuclear/PET: Myocardial Perfusion Imaging	30-Apr-21
3	2	Charlton Trinidad, M.D.	1739 East Main Street	Suite Ab	Prattville	AL	36066	Charlton Trinidad, M.D.	Nuclear/PET: Myocardial Perfusion Imaging	30-Apr-21
4	3	MEDICAL CARE ASSOCIATES PRATTVILLE	102 MEDICAL CENTER DR SUITE A		Prattville	AL	36066	MEDICAL CARE ASSOCIATES PRATTVILLE	Nuclear/PET: Myocardial Perfusion Imaging	30-Apr-21
5	4	Primary Care Internists	1722 Pine Street		Montgomery	AL	36106	Primary Care Internists	Nuclear/PET: Myocardial Perfusion Imaging	30-Apr-21
6	5	John K. L. Porter, D.O., PC	1758 Park Pl Suite 100		Montgomery	AL	36106	John K. L. Porter, D.O., PC	Nuclear/PET: Myocardial Perfusion Imaging	30-Apr-21
7	6	William O. Sargeant, PC	1758 Park Pl Suite 100		Montgomery	AL	36106	William O. Sargeant, PC	Nuclear/PET: Myocardial Perfusion Imaging	30-Apr-21
8	7	Mulberry Medical Associates	1301 Mulberry Street		Montgomery	AL	36106	Mulberry Medical Associates	Nuclear/PET: Myocardial Perfusion Imaging	30-Apr-21
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=getZip([@[Street Address]],[@City],[@State])

Option Explicit

Function getZip(street As String, city As String, state As String) As String
street = Replace(street, " ", "+")
Dim url As String:                  url = "https://m.usps.com/m/QuickZipAction?mode=0&tAddress=" & street & "&tApt=&tCity=" & city & "&sState=" & state & "&jsonInd=Y"
Dim result As String:               result = GetData(url)

With CreateObject("VBScript.RegExp")
    .Pattern = """zip""\s:\s""(\d{5})"
    getZip = .Execute(result)(0).submatches(0)
End With
End Function

Function GetData(myUrl As String) As String
    Dim winHttpReq As Object

    Set winHttpReq = CreateObject("Microsoft.XMLHTTP")
    winHttpReq.Open "GET", myUrl, False
    winHttpReq.Send
    GetData = winHttpReq.ResponseText
End Function

 

[lrobbo314]

And just for fun, you can do the same thing with Power Query as well.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Order	Practice Name	Street Address	Address line 2	City	State	Web link	Accred1	Accred1 Date
2	1	Selma Family Care, PC	203 Vaughan Memorial Drive		Selma	AL	Selma Family Care, PC	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
3	2	Charlton Trinidad, M.D.	1739 East Main Street	Suite Ab	Prattville	AL	Charlton Trinidad, M.D.	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
4	3	MEDICAL CARE ASSOCIATES PRATTVILLE	102 MEDICAL CENTER DR SUITE A		Prattville	AL	MEDICAL CARE ASSOCIATES PRATTVILLE	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
5	4	Primary Care Internists	1722 Pine Street		Montgomery	AL	Primary Care Internists	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
6	5	John K. L. Porter, D.O., PC	1758 Park Pl Suite 100		Montgomery	AL	John K. L. Porter, D.O., PC	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
7	6	William O. Sargeant, PC	1758 Park Pl Suite 100		Montgomery	AL	William O. Sargeant, PC	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
8	7	Mulberry Medical Associates	1301 Mulberry Street		Montgomery	AL	Mulberry Medical Associates	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
9
10
11	Order	Practice Name	Street Address	Address line 2	City	State	Zip	Web link	Accred1	Accred1 Date
12	1	Selma Family Care, PC	203 Vaughan Memorial Drive		Selma	AL	36701	Selma Family Care, PC	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
13	2	Charlton Trinidad, M.D.	1739 East Main Street	Suite Ab	Prattville	AL	36066	Charlton Trinidad, M.D.	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
14	3	MEDICAL CARE ASSOCIATES PRATTVILLE	102 MEDICAL CENTER DR SUITE A		Prattville	AL	36066	MEDICAL CARE ASSOCIATES PRATTVILLE	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
15	4	Primary Care Internists	1722 Pine Street		Montgomery	AL	36106	Primary Care Internists	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
16	5	John K. L. Porter, D.O., PC	1758 Park Pl Suite 100		Montgomery	AL	36106	John K. L. Porter, D.O., PC	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
17	6	William O. Sargeant, PC	1758 Park Pl Suite 100		Montgomery	AL	36106	William O. Sargeant, PC	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
18	7	Mulberry Medical Associates	1301 Mulberry Street		Montgomery	AL	36106	Mulberry Medical Associates	Nuclear/PET: Myocardial Perfusion Imaging	4/30/2021
Sheet2

PQ getZip function

(address as text, city as text, state as text) as table =>

let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://m.usps.com/m/QuickZipAction?mode=0&tAddress=" & address & "&tApt=&tCity=" & city & "&sState=" & state & "&jsonInd=Y"), null, null, 28591)}),
    Replace = Table.ReplaceValue(Source,"""","",Replacer.ReplaceText,{"Column1"}),
    Split = Table.SplitColumn(Replace, "Column1", Splitter.SplitTextByDelimiter("zip : ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    Remove = Table.RemoveColumns(Split,{"Column1.1"}),
    Rename = Table.RenameColumns(Remove,{{"Column1.2", "Zip"}}),
    CleanUp = Table.TransformColumns(Rename,{"Zip", each Text.Start(_,5)})
in
    CleanUp

PQ Table Transformations

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReplaceSpaces = Table.AddColumn(Source, "Custom", each Text.Replace([Street Address]," ","+")),
    GetZip = Table.AddColumn(ReplaceSpaces, "Zip", each getZip([Custom], [City], [State])),
    Expand = Table.ExpandTableColumn(GetZip, "Zip", {"Zip"}, {"Zip"}),
    Rename = Table.RemoveColumns(Expand,{"Custom"}),
    Reorder = Table.ReorderColumns(Rename,{"Order", "Practice Name", "Street Address", "Address line 2", "City", "State", "Zip", "Web link", "Accred1", "Accred1 Date"})
in
    Reorder

 

[lrobbo314]

That last Power Query function can be improved a bit. I dug around and found that Power Query can easily parse JSON. And it allows me to return the zip code value as just a text value instead of returning an entire table just for 1 bit of text. Same results as above just with some optimized code.

PQ Function

(address as text, city as text, state as text) as text =>

let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://m.usps.com/m/QuickZipAction?mode=0&tAddress=" & address & "&tApt=&tCity=" & city & "&sState=" & state & "&jsonInd=Y"), null, null, 28591)}),
    zipValue = Table.AddColumn(Source, "Custom", each Json.Document([Column1])){0}[Custom][addresses]{0}[zip]
in
    zipValue

PQ Table Transformations

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    addZipColumn = Table.AddColumn(Source, "Zip", each getZip(Text.Replace([Street Address]," ","+"),[City],[State])),
    Reorder = Table.ReorderColumns(addZipColumn,{"Order", "Practice Name", "Street Address", "Address line 2", "City", "State", "Zip", "Web link", "Accred1", "Accred1 Date"})
in
    Reorder

 

[lrobbo314]

Also, I got home and hopped on Excel 365 and, admittedly pretty cool, it doesn't give you zip codes. It gives you cool info on city and state values though. Population, latitude, longitude, area, leaders, time zones, capital, household median income, etc.

 

[Myhobohemia]

Thank you so much! You guys have been mentioning Power Query on this thread but I had never heard of it before. I have no idea how and where to paste that code. I did launch the Power Query editor, created a Blank Query, and pasted that last code into it, the one for the PQ getZip function. It appears to be saved. But I have no idea how to connect it with the data source or make it run yet. But ... baby steps. I'm on it.

 

[Myhobohemia]

Is it necessary to have Power BI Desktop installed to use Power Query properly? Just curious.

 

[lrobbo314]

No, you don't need BI desktop. Depending on the version you have it can come pre-installed. I'm on mobile now so I can't do a step by step. Gimme an hour or so when I get to my desk I'll lay it out.

Don't forget about the VBA to get the zip code that I posted. Works just as well.