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]

In Excel, under the 'Data' tab, click on 'New Query' and under 'Other Sources' select 'Blank Query'.

from there you can just paste this code for the function.

code
(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

Then, in Power Query, click on 'Close & Load'.

Then go to where your table is that needs the zip codes and again, under the 'Data' tab -> 'Get & Transform' click on 'From Table'. Make sure that the active cell is somewhere inside the table.

Once there, you can click on 'Advanced Editor' and paste the code for the table transformations.

Let me know if you have any questions.

 

[sandy666]

from there you can just paste this code for the function.

code
(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

Then, in Power Query, click on 'Close & Load'.

you forgot tags for bolded text ?

 

[lrobbo314]

Right you are. Good lookin' out.

 

[Myhobohemia]

Thanks to all of you. Frankly I'm struggling, and for increasingly dumb reasons. For example, I can't create and save a macro now. When I did it fifty times last week. It won't hold.

I was trying to use the VBA code, rather than Power Query. My steps were these:
-Open document
-Visual Basic
-Insert
-Copy-paste
-Save

Unless I'm hallucinating, when I did that before, I could open "Macros" and it would be there. Now it's totally blank. What am I doing wrong?

 

[Fluff]

Any procedure that requires an argument (the bits inside the brackets after the procedure name) will not appear in the list when you click Macros, as they cannot be run manually.

 

[Myhobohemia]

Ah ok! So what do I need to do for it to retrieve the data and put it in the zip code column? I literally don't know what to click on.

As for my Power Query attempt, it went like this. I crashed and burned here too. Spelling out the steps explicitly:

-Open Excel document
-Data
-Get Data
-Launch Power Query Editor
(now inside PQE / New Query
-New Query
-New Source
-Other Sources
-Blank Query
-(copy-pasted your code)

Here is the image. Maybe I left something in or out which shouldn't have been.

So then I hit "Close & Load", which opened up a new sheet and put the code in it, like this.

I don't know the next step from here. The instructions where these:

"Then go to where your table is that needs the zip codes and again, under the 'Data' tab -> 'Get & Transform' click on 'From Table'. Make sure that the active cell is somewhere inside the table.

Once there, you can click on 'Advanced Editor' and paste the code for the table transformations. "

... but I'm sorry to say I don't understand. I'm not sure which sheet I should be on, whether anything needs to be selected....

Should I be on the sheet with the data? If so, where? Am I supposed to paste the active cell on the same spreadsheet as the data?

My apologies for this!

 

[Fluff]

For the functions, you call them from the sheet as shown in post#54.
For PQ I wouldn't have a clue, I'll leave that to @lrobbo314

 

[Myhobohemia]

I saw this but didn't know what to do with it. ☹

Where do I have to go to copy-paste it? Sorry this is so basic.

 

[Fluff]

You'd simply enter that into a cell, the same way as a standard excel function, if your data isn't in a table it would look like

+Fluff.xlsm
	A	B	C	D	E	F	G
1	Order No	Practice Name	Street Address	Address line 2	City	State	Zip Code
2	1	Selma Family Care, PC	203 Vaughan Memorial Drive		Selma	AL	36701
3	2	Charlton Trinidad, M.D.	1739 East Main Street	Suite Ab	Prattville	AL	36066
4	3	MEDICAL CARE ASSOCIATES PRATTVILLE	102 MEDICAL CENTER DR SUITE A		Prattville	AL	36066
5	4	Primary Care Internists	1722 Pine Street		Montgomery	AL	36106
6	5	John K. L. Porter, D.O., PC	1758 Park Pl Suite 100		Montgomery	AL	36106
7	6	William O. Sargeant, PC	1758 Park Pl Suite 100		Montgomery	AL	36106
8	7	Mulberry Medical Associates	1301 Mulberry Street		Montgomery	AL	36106
Sheet3

Cell Formulas
Range	Formula
G2:G8	G2	=getzip(C2,E2,F2)

 

[Myhobohemia]

Ah! Thank you. For the record, I did try that before. This happened. ?