Docs/Guides/Formula recipes

Formula recipes

Copy-and-paste formulas for the jobs people actually bring to a spreadsheet — enriching IPs, cleaning email and phone lists, converting prices, checking domains, ages, addresses, VINs and text.

View as Markdown

Each recipe below is a working formula against a real source, with the column you put your data in and the value you get back. Paste it, point it at your own column, and it runs.

Two conventions throughout:

  • Your data starts in A2, under a header row. Change the reference to match your sheet.
  • Google Sheets fills the whole column in one formula (A2:A100), and Excel takes one cell and is dragged down (A2). Both are batched behind the scenes — see refresh and recalculation for how that keeps the cost down.

Every value used here is included on every plan. Where a source also has fields that need a paid plan, the cell says #PREMIUM(field) rather than going blank — see errors.

Where are these IP addresses?

A column of IPs from a signup form, a server log or an ad report. One formula per column you want back:

=VERVE("iplookup", A2:A100, "field", "countryName")
=VERVE("iplookup", A2:A100, "field", "regionName")
=VERVE("iplookup", A2:A100, "field", "timezone")

Each answer is kept for five minutes and reused, so a second or third column added over the same IPs a minute later is served from the first one's lookups rather than charged again.

country gives the two-letter code (US) where countryName gives the name — the code is the better choice if you are going to pivot or COUNTIF on it.

Clean an email list

Before an import, a campaign or a CRM sync, mark the addresses that will bounce or that nobody reads:

=VERVE("emailvalidator", A2:A500, "field", "isValid")
=VERVE("emailvalidator", A2:A500, "field", "isDisposable")
=VERVE("emailvalidator", A2:A500, "field", "isFreeEmail")

The cells hold real TRUE / FALSE values, so a filter, COUNTIF(B2:B500, FALSE) or a conditional-format rule works on them directly.

To turn the checks into one decision per row, nest it:

=IF(VERVE("emailvalidator", A2, "field", "isValid"), "keep", "remove")

isRoleAccount flags shared inboxes such as support@ and info@, and isCompanyEmail separates work addresses from personal ones — useful for scoring B2B leads.

A list you check once should not stay live

An email list is checked once, not watched. Once the column is filled, keep the answers and drop the formulas: select the column and use the sidebar's Values mode, or copy and paste values only. Nothing recalculates after that, and nothing is charged again.

Normalise phone numbers

Phone numbers typed by hand arrive in every shape. This turns each one into the international +18165551017 form that dialers, SMS tools and CRMs expect. The country is the second input, as a two-letter code:

=VERVE("phonenumbervalidator", A2:A200, "us", "field", "formatted.e164")
=VERVE("phonenumbervalidator", A2:A200, "us", "field", "isValid")
=VERVE("phonenumbervalidator", A2:A200, "us", "field", "isMobile")

If the country varies per row, put it in column B and pass B2:B200 (or B2 in Excel) instead of "us". A single value next to a range is used for every row; a second range is read row by row.

formatted.national gives the local (816) 555-1017 form instead.

Convert a price column

The direct way converts every row:

=VERVE("currencyconverter", A2:A50, "USD", "EUR", "field", "convertedValue")

That is a lookup per distinct amount. When every row uses the same pair of currencies, it is cheaper to look the rate up once and multiply — put this in one cell, say E1:

=VERVE("exchangerate", "USD", "EUR", "field", "exchangeRate")

Then =A2*$E$1 down the column. One lookup prices the whole sheet, and the column updates when the rate does.

For a single figure there is a shorthand that takes the amount and the two currencies and nothing else:

=VERVEFX(B2, "USD", "GBP")

Check a list of domains

For an agency, a security review or a portfolio of client sites — when each domain expires, who it is registered with, and whether its certificate is still good:

=VERVE("domainexpiration", A2:A40, "field", "daysToExpiration")
=VERVE("whoislookup", A2:A40, "field", "registrar")
=VERVE("sslchecker", A2:A40, "field", "valid_to")
=VERVE("sslchecker", A2:A40, "field", "isExpired")

daysToExpiration is a plain number, so a conditional-format rule of "less than 30" turns the renewals you are about to miss red. Write the domain without https:// or a path — example.com, not https://example.com/about.

Two more that answer "is this domain set up for email?":

=VERVE("dnslookup", A2:A40, "field", "summary.hasMailServers")
=VERVE("whoislookup", A2:A40, "field", "domainAgeYears")

A domain registered last month with no mail servers is a common pattern in fake signups.

Local time for a distributed team

A column of cities, and what time it is there:

=VERVE("timezonelookup", A2:A20, "field", "time12")
=VERVE("timezonelookup", A2:A20, "field", "timezone")

The time is the time of the lookup. A formula does not tick like a clock; it moves when the cell recalculates — see refresh and recalculation.

Ages from birth dates

agecalculator wants the date as text in YYYY-MM-DD form. A date cell is a number underneath, so convert it on the way in:

=VERVE("agecalculator", TEXT(A2, "yyyy-mm-dd"), "field", "age_breakdown.years")
=VERVE("agecalculator", TEXT(A2, "yyyy-mm-dd"), "field", "next_birthday.days")

The same TEXT() wrapper works for any source that takes a date. Without it, the source receives the date's internal serial number or a long date string, which it cannot read as a date.

Split and complete addresses

Pull the parts out of a one-line address:

=VERVE("streetaddressparser", A2:A100, "field", "parsed.zipCode")
=VERVE("streetaddressparser", A2:A100, "field", "parsed.cityName")

Or start from a ZIP code and fill in the rest:

=VERVE("zipcodes", A2:A100, "field", "city")
=VERVE("zipcodes", A2:A100, "field", "state")
ZIP codes lose their leading zero

A spreadsheet stores 02134 as the number 2134, and that is what gets looked up. Format the column as plain text before you type or paste the codes, or pass TEXT(A2, "00000") instead of A2.

Decode vehicle VINs

For a fleet list, a dealer stock sheet or an insurance intake:

=VERVE("vindecoder", A2:A60, "field", "make")
=VERVE("vindecoder", A2:A60, "field", "model")
=VERVE("vindecoder", A2:A60, "field", "year")

valid comes back FALSE for a VIN with a bad check digit — usually a typo in the column, and worth filtering on before anything else.

Read a column of text

Survey answers, reviews or support tickets — the tone of each one, and what language it is in:

=VERVE("sentimentanalysis", A2:A300, "field", "sentimentText")
=VERVE("languagedetector", A2:A300, "field", "primaryLanguage")

And translate it. The target language is a code such as es, fr or de:

=VERVE("translator", A2:A300, "es", "field", "translatedText")

Translation costs more per row than most sources, and it is exactly the kind of column that should be filled once with Values rather than left live.

A small live dashboard

Live formulas earn their keep on numbers you want current every time you open the file. A few single cells:

=VERVE("goldprice", "field", "ounce")
=VERVE("silverprice", "field", "ounce")
=VERVE("exchangerate", "USD", "JPY", "field", "exchangeRate")
=VERVE("weatherforecast", "Chicago", "field", "tempF")

Point the inputs at a settings cell instead of typing them, and the whole board follows when someone changes it — put the city in B1 and write =VERVE("weatherforecast", $B$1, "field", "tempF").

Every one of these is an ordinary number, so it goes straight into a chart, a sparkline or a calculation:

=ROUND(VERVE("goldprice", "field", "ounce") / VERVE("silverprice", "field", "ounce"), 1)

That is the gold-to-silver ratio, from two lookups.

Unit conversion in bulk

A column of weights in pounds, as kilograms:

=VERVE("unitconverter", A2:A100, "lb", "kg", "field", "result.result")

Skip the rows that failed

A failed row holds text starting with #ERR, so anything that should ignore failures can test for it:

=IF(LEFT(B2, 4) = "#ERR", "", B2)
=COUNTIF(B2:B500, "#ERR*")

The second counts the failures in a filled column — a quick check before you hand the sheet to someone else. Errors lists every message and what fixes it.

Find a source that is not here

Every recipe follows the same three steps:

  1. Find the source in the sidebar or in all sources. Search by what you want — "vat", "holiday", "airport" — rather than by name.
  2. Read its inputs off its page, in order. Those are the arguments after the source name.
  3. Pick the value from its list of fields and pass it as "field".

If you are not sure which field you want, leave "field" out once in a spare cell: the whole response spills out as two columns, every field next to its value. Read the one you want, then write the real formula.

Next

Formulas has the full grammar these recipes use. Refresh and recalculation explains when a formula runs again and what that costs, and troubleshooting covers the cell that will not cooperate.

Was this page helpful?
PreviousSecurityNextRefresh & recalculation

Last updated