VerveSheets
Formulas for

Parse Addresses

Parsers2 credits/cellSheets · Excel
=VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "field", "parsed.streetNumber")
What this pulls

Splits a full US street address into its parts — house number, street name, street type, city, state and ZIP — so each lands in its own column. It reads the address the way a person would, which is why it copes with the inconsistent commas and spacing that break TEXTSPLIT and LEFT/MID/FIND formulas.

It's one of 250+ live sources you reach with a single =VERVE() function — served from APIVerve's production data engine, billed at 2 credits per resolved cell, and refreshed on whatever cadence you set. No API keys to wrangle, no scripts, no exports.

See it in a cell

A real example — the input you type, and the values you can pull from it. Click any cell to see the formula behind it.

streetaddressparser.xlsxsample data
B2fx=VERVE("streetaddressparser", A2, "field", "parsed.streetNumber")
A
B
C
D
E
1
Address
Parsed › Street Number
Parsed › Street Type
Parsed › Street Address
Parsed › City Name
2
1600 Amphitheatre Parkway, Mountain View, CA 90210
1600
Pkwy
Amphitheatre
Mountain View
3
4
click any cell to see its formula2 credits / cell
Ways to use it

Split a mailing list into columns

Turn one messy address column into separate street, city, state and ZIP columns ready for a mail merge or a shipping import.

Clean CRM exports

Normalize addresses that were typed in by hand so you can sort, dedupe and group customers by city or state.

Pair it with ZIP lookup

Pull the ZIP out of each address, then check it against the ZIP Code Lookup's city to catch addresses that don't add up.

Use it in your sheet

The same function works in both apps — install once, sign in with your key, and type it into any cell.

Google SheetsAdd-on

Install from the Workspace Marketplace, then drop the formula into a cell. Autocomplete suggests the source and its fields.

=VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "field", "parsed.streetNumber")
Microsoft ExcelAdd-in

Same source, same arguments, same key — in Excel on desktop, the web, or mobile. Excel namespaces it as VERVE.CALL. Drag to fill a whole column.

=VERVE.CALL("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "field", "parsed.streetNumber")
Fields you can pull

Add a field name as the last argument to land just that value in the cell.

  • Parsed › Street Number
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "field", "parsed.streetNumber")
  • Parsed › Street Type
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "field", "parsed.streetType")
  • Parsed › Street Address
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "field", "parsed.streetAddress")
  • Parsed › City Name
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "field", "parsed.cityName")
  • Parsed › State Name
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "field", "parsed.stateName")
  • Parsed › Zip Code
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "field", "parsed.zipCode")
  • Address
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "field", "address")

Parse Addresses, answered

Everything you need to wire it into a sheet.

How do I split an address into city, state and ZIP in Excel?
With the full address in A2, put =VERVE.CALL("streetaddressparser", A2, "field", "parsed.cityName") in B2, the same with "parsed.stateName" in C2 and "parsed.zipCode" in D2. In Google Sheets use =VERVE() in place of =VERVE.CALL().
Why not just use TEXTSPLIT or Text to Columns?
Those split on a character, so they only work when every address has the same commas in the same places. Real lists don't — some have a unit number, some miss a comma, some put the ZIP on the same chunk as the state. The parser understands the address itself, so the columns line up either way.
How do I get the street line on its own?
The street comes back in parts — "parsed.streetNumber", "parsed.streetAddress" (the street name) and "parsed.streetType" (St, Ave, Pkwy). Join them with & " " & to rebuild the line, or keep them separate for matching.
How do I pull Parse Addresses into Google Sheets?
Install the VerveSheets add-on, sign in once with your key, and type =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "field", "parsed.streetNumber") into any cell. The "field" argument names the value you want back, so the cell resolves to a single number or string you can chart, sort, or feed into another formula.
What data can I pull from Parse Addresses?
Parse Addresses returns parsed.streetNumber, parsed.streetType, parsed.streetAddress, parsed.cityName and parsed.stateName, plus 2 more — name any one of them as the "field" argument and that value lands in the cell. Leave the field out and the whole record spills into a two-column table instead.
Can I fill a whole column of Parse Addresses at once?
Yes. Put your address values down a column and point the formula at the range — A2:A50 instead of A2 — and one call returns a value for every row. Dragging the formula down works too, but the range form is a single billed call per column rather than one per cell.
How many credits does each Parse Addresses cell cost?
Each resolved cell costs 2 credits. A cell only re-bills when it actually refreshes, so a sheet you aren't touching doesn't keep charging.
Does Parse Addresses work in Excel as well as Google Sheets?
Yes — same arguments, same key, same fields. Excel namespaces custom functions, so the call is =VERVE.CALL() there and =VERVE() in Google Sheets; nothing else about it changes.
Combine it with
Parse Names
=VERVE("humannameparser", "MR. MARTINO PETROS DE FLORENCE (TINO) Jr.", "field", "parsed.title")
RSS Feeds to JSON
=VERVE("rsstojson", "https://www.nasa.gov/rss/dyn/breaking_news.rss", "field", "source")
Browse all sources

Put Parse Addresses in your next spreadsheet. Add the extension, paste the formula, and the cell keeps itself current.

Add to Google Sheets→Add to Excel

Scaling up?

Volume pricing, custom SLAs, and dedicated support for high-traffic teams.

Contact sales