Formulas for

Parse Addresses

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

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.

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, "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

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.

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", "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", "parsed.streetNumber")

Tune the result by adding name=value before the field argument — in any order. Leave one out and its default applies.

  • standardize

    Add a standardized block: the address type, plus on paid plans the delivery line, last line and ZIP+4

    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "parsed.streetNumber", "standardize", "true")

Add a field name right after the inputs to land just that value in the cell.

  • Parsed › Street Number
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "parsed.streetNumber")
  • Parsed › Street Type
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "parsed.streetType")
  • Parsed › Street Address
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "parsed.streetAddress")
  • Parsed › City Name
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "parsed.cityName")
  • Parsed › State Name
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "parsed.stateName")
  • Parsed › Zip Code
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "parsed.zipCode")
  • Standardized
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "standardized")
  • Standardized › Address Type
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "standardized.addressType")
  • Standardized › Delivery Line1Premium
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "standardized.deliveryLine1")
  • Standardized › Last LinePremium
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "standardized.lastLine")
  • Standardized › Zip Plus4Premium
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "standardized.zipPlus4")
  • Address
    =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "address")

Premium marks data included on paid plans. On the Free plan a marked field is withheld and a marked input is ignored — either way the cell reads #PREMIUM(name) instead of a value, so a gated answer can never be mistaken for a real one. Everything unmarked works on every plan. Compare plans

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, "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", "parsed.streetNumber") into any cell. The argument after the inputs 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 4 more — name any one of them after the inputs and that value lands in the cell. Leave the field out and the whole record spills into a two-column table instead.
Can I narrow what Parse Addresses returns?
Yes — Parse Addresses takes "standardize" as optional inputs. They go in as "name", value pairs after the inputs and the field, in any order, so =VERVE("streetaddressparser", "1600 Amphitheatre Parkway, Mountain View, CA 90210", "parsed.streetNumber", "standardize", "true") shapes the result without changing anything else.
Which Parse Addresses fields need a paid plan?
standardized.deliveryLine1, standardized.lastLine and standardized.zipPlus4 are included from the Starter plan up. On the Free plan the cell returns #PREMIUM(field) rather than a wrong or empty value, so it's always obvious which one to upgrade for. Every other field works on Free.
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.

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

Scaling up?

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

Contact sales