Jacob J. Walker's Blog

Scholarly Thoughts, Research, and Journalism for Informal Peer Review

Creating a Link to USPS Zip+4 Lookup in Excel

without comments

I am working on mailing a large number of organizations (which I will talk more about soon in another post), and the list I have in Excel of their mailing addresses often doesn’t include the Zip+4, which I originally thought was needed the business rate for First Class Mail. (Since my original posting, I read the “fine print” more and it seems that a standard ZIP code is sufficient for the rate, as long as I can put the appropriate bar code on the envelope.)

While I would ultimately like to make a full web scraper to automatically download the Zip+4, unfortunately Excel has limitations about how User Defined Functions (UDFs) and QueryTable can work with each other (specifically, I want to create a temporary sheet to download a URL into, to then get a particular cell, and UDFs just can’t do that.)

So as a temporary workaround until I can program something either in Python or write some VBA code that isn’t a UDF, I have created a formula that creates a link to the USPS Zip+4 Lookup webpage.  For those who are interested, here is the formula:

=HYPERLINK(“https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=”&SUBSTITUTE([@ADDR],” “,”+”)&”&address2=&city=”&SUBSTITUTE([@CITY],” “,”+”)&”&state=”&[@STABBR]&”&urbanCode=&postalCode=&zip=”,”USPS Lookup”)

I should note that this seems to handle having address1 and address2 combined in the same field of ADDR.  You could modify the formula a little if you have separate fields in your spreadsheet for address1 and address2.

Update: It looks like if you do this too many times you get the following message:

To learn about integrating the free Postal Service® Address and Tracking API’s into your application, please visit www.usps.com/webtools.

I will investigate this more, and see how the API might work with Excel.

Post Revisions:

Written by Jacob Walker

November 19th, 2013 at 5:39 am

Leave a Reply

%d bloggers like this: