Zip Code Lookup API

Imagine being able to type a zip code into a cell in Excel and have the corresponding city and state automatically fill in. No manual entry, no lookup tables … just a smart, seamless automation. In this walkthrough, we’ll explore how to tap into a free zip code API using Excel’s WEBSERVICE function, and then parse the results using text functions to extract the city and state. This approach is perfect for enhancing data entry forms or automating customer address processing tasks.

Video

Overview of the Free Zip Code API

We’ll use the following free API for zip code data:

https://api.zippopotam.us/us/ZIPCODE

Simply replace ZIPCODE with a valid U.S. zip code, like this:

https://api.zippopotam.us/us/90210

The API returns a JSON-formatted response that includes the city (listed as place name) and state details.

You can easily test it by entering the URL into your favorite browser:

Step-by-Step Guide to Automating Zip Code Lookups

Step 1: Use WEBSERVICE to Retrieve API Response

In Excel, we use the WEBSERVICE function to call the API and retrieve the response.

In a worksheet, let’s start with a cell where the user can enter the zip code, such as C8:

In another cell, like B11, let’s write our formula:

=WEBSERVICE("http://api.zippopotam.us/us/" & C8)

This retrieves the entire JSON response into the cell:

Step 2: Extract the State Abbreviation

The state abbreviation is typically the last piece of text in the response.

We’ll extract it using MID and LEN and write the following formula in C10:

=MID(B11,LEN(B11)-5,2)

We hit Enter, and bam:

Step 3: Extract the City Name (Place Name)

In summary, we will retrieve the primary city name based on extracting text betewen the position of the “place name” label and the “longitude” label. Here’s the formula along with an explainer.

=LET(
api,B11,
place_start,SEARCH("place name",api)+14,
place_end,SEARCH("longitude",api)-4,
place_len,place_end-place_start,
place,MID(api,place_start,place_len),
place
)

For reference, here is the results of the WEBSERVICE function stored in B11

{"country": "United States", "country abbreviation": "US", "post code": "90210", "places": [{"place name": "Beverly Hills", "longitude": "-118.4065", "latitude": "34.0901", "state": "California", "state abbreviation": "CA"}]}

Step-by-step breakdown

1. LET

Signature:

LET(name1, value1, [name2, value2]…, calculation)

• api, B11 → stores the entire API string in a variable called api.

So now api = the JSON text above.

2. Find the start of "place name"

SEARCH("place name", api) + 14

• SEARCH(find_text, within_text)
• Finds "place name" in the string.
• The match starts at the p in "place name".
• Adding 14 skips past place name": " (13 characters) so the pointer lands on the first letter of the value, which is B in "Beverly Hills".

This becomes place_start.

3. Find the end of the place name

SEARCH("longitude", api) - 4

• Finds where "longitude" starts in the string.
• Subtracting 4 moves the pointer backward to just before the comma and quotation mark that close the "place name" value.

This becomes place_end.

4. Calculate the length of the place name

place_end - place_start

This gives the exact number of characters between start and end.
For "Beverly Hills", that’s 13 characters.

This becomes place_len.

5. Extract the place name

MID(api, place_start, place_len)

Signature:

MID(text, start_num, num_chars)

• text → the API string (api).
• start_num → place_start (position of B in "Beverly Hills").
• num_chars → place_len (13).

This returns:

Beverly Hills

This is stored in the variable place.

6. Return the value

The final argument to LET is just place, so the whole formula returns:

Beverly Hills

✅ Formula Summary

The formula extracts the "place name" value (Beverly Hills) from the JSON API response in B11 by:

1. Locating "place name"
2. Skipping to the actual value
3. Finding where "longitude" begins
4. Backing up to the end of the place name
5. Using MID to pull out exactly those characters

We can then easily swap out the WEBSERVICE function for the static B11 value as follows:

=LET(
api,WEBSERVICE("https://api.zippopotam.us/us/"&C8),
place_start,SEARCH("place name",api)+14,
place_end,SEARCH("longitude",api)-4,
place_len,place_end-place_start,
place,MID(api,place_start,place_len),
place
)

And we can also to the LET function to extract the state abbreviation:

=LET(
api,WEBSERVICE("https://api.zippopotam.us/us/"&C8),
state,MID(api,LEN(api)-5,2),
state
)

And when you type in a zip code, Excel dynamically and automatically retrieves the primary city and state:

Step 5: Format Zip Code Input as Text

If zip codes start with a zero (like 01101), Excel may strip the leading zero if it’s treated as a number. To prevent this:

1. Select the zip code input cell (e.g., C8).
2. Right-click > Format Cells > Set format to Text.

Step 6: Create a Full Address Entry Form

Now we can build a form with columns like:

• Name
• Street Address
• Zip Code (input)
• City (automated)
• State (automated)

Using the WEBSERVICE + LET combination for both city and state extraction, users will enjoy a responsive lookup experience without needing to maintain reference tables.

Summary

By using a free online API and Excel’s WEBSERVICE, LET, SEARCH and MID functions, we can build a robust and dynamic zip code lookup system directly within Excel. This is especially valuable for forms or datasets that require standardized address information without maintaining local lookup tables.

The ability to pull data from the web and parse it effectively opens up a wide array of possibilities—and zip code lookups are just the beginning!

We hope this guide helped you advance your workbook automation! Try plugging this into your next project or form and see how it enhances your workflow. Happy Excelling!

Download the Demo File

Frequently Asked Questions (FAQs)

1. Do I need an API key to use this service?

No, the zippopotam.us API does not require authentication or an API key for casual use.

2. Can this method work for countries outside the US?

Yes! Replace /us/ in the API URL with the appropriate country code (e.g., /ca/ for Canada).

3. What if my zip code starts with a zero?

Be sure to format your zip code cell as “Text” in Excel to preserve leading zeros.

4. Can I extract more information from the API?

Yes, the API returns latitude, longitude, and other place attributes. You can parse these using similar techniques.

5. Are there any limitations to the WEBSERVICE function?

Yes, WEBSERVICE only works with text-based content. It cannot parse JSON or XML natively—so creative parsing via formulas is key.

6. Will this work on Excel for Mac?

The WEBSERVICE function is supported on Excel for Windows. Excel for Mac may not support WEBSERVICE natively.

7. How can I handle errors for invalid zip codes?

You can wrap formulas in IFERROR() to handle cases where the API returns empty results or errors.

8. Can I hide the full API JSON response?

Yes. Once the final city and state formulas are working, you can delete or hide the helper cells to clean up your sheet.

9. Is this method dynamic?

Absolutely. New zip codes entered in the input cell will trigger the lookup and return updated results automatically.

10. What if the API goes down?

Since Excel is retrieving live data, if the API is offline or slow, the lookup may fail or delay. Always have a fallback or manual method if critical.