How to Extract URLs and Domains from Text in Google Sheets
Formulas that pull addresses out of a text column, get the hostname, and — importantly — why the popular "root domain" formula is wrong for half the internet.
These are for text that contains addresses — a notes column, a message export, a description field. If your cells are hyperlinks rather than text, start with the hyperlink guide instead.
Google Sheets uses RE2 for its regular expressions, which is worth knowing because RE2 has no backreferences and no lookbehind. The patterns below stay inside what it supports.
The first URL in a cell
=IFERROR(REGEXEXTRACT(A2, "https?://[^\s,;""'<>)\]]+"), "")The character class ends the match at whitespace and at the punctuation that usually surrounds a link in prose. A trailing full stop is handled by the next formula.
That class is doing the same job as the trimming step in the text extractor: stopping before the punctuation that follows a link in a sentence. It is not as careful — it cannot count brackets, so .../Foo_(bar) loses its closing bracket — but it handles the common cases.
To also drop a trailing full stop or comma:
=IFERROR(
REGEXREPLACE(
REGEXEXTRACT(A2, "https?://[^\s,;""'<>)\]]+"),
"[.,;:!?]+$", ""
),
""
)Every URL in a cell
REGEXEXTRACT returns one match. For all of them, split the text into words and keep the ones that look like addresses:
=TRANSPOSE(
FILTER(
SPLIT(A2, " " & CHAR(10), TRUE, TRUE),
REGEXMATCH(SPLIT(A2, " " & CHAR(10), TRUE, TRUE), "^https?://")
)
)SPLIT with `CHAR(10)` in the delimiter set handles line breaks inside a cell. The third argument splits on each character of the delimiter string; the fourth removes empty results.
This is good enough for tidy data and gets awkward quickly: an address followed immediately by a comma with no space stays attached to the word, and a URL containing a space (rare, but encoded ones exist) is split. For a column of messy text, pasting it into the text extractor is both faster and more accurate.
Getting the hostname
This one is straightforward, and safe:
=IFERROR(REGEXEXTRACT(A2, "^(?:https?://)?(?:www\.)?([^/?#:]+)"), "")Handles addresses with or without a scheme, drops a leading www., and stops at the first /, ?, # or : so a port is not included.
| Hostname | Last two labels | Correct registrable domain |
|---|---|---|
| shop.example.co.in | `co.in` ❌ | `example.co.in` |
| www.bbc.co.uk | `co.uk` ❌ | `bbc.co.uk` |
| deep.sub.example.com.au | `com.au` ❌ | `example.com.au` |
| me.github.io | `github.io` — arguable | Depends what you mean |
| news.example.com | `example.com` ✓ | `example.com` |
The reason is that co.in, co.uk and com.au are public suffixes — parts of the namespace under which anyone can register. Deciding where a registrable domain begins requires the Public Suffix List, which is thousands of rules long and changes over time.
A spreadsheet formula cannot consult that list. So there are only two honest options.
Option A — accept a partial fix. If your data is entirely .com, .org and .net, last-two-labels is correct, and a small hardcoded exception list handles a few known suffixes:
=LET(
host, IFERROR(REGEXEXTRACT(A2, "^(?:https?://)?(?:www\.)?([^/?#:]+)"), ""),
parts, SPLIT(host, "."),
n, COUNTA(parts),
secondLast, IF(n >= 2, INDEX(parts, 1, n - 1), ""),
IF(n < 2, host,
IF(REGEXMATCH(secondLast, "^(co|com|net|org|gov|edu|ac|or|ne)$"),
IF(n >= 3,
INDEX(parts,1,n-2) & "." & INDEX(parts,1,n-1) & "." & INDEX(parts,1,n),
host),
INDEX(parts,1,n-1) & "." & INDEX(parts,1,n)
)
)
)This covers the common second-level patterns (co.uk, co.in, com.au, org.uk, ac.in and similar). It is a heuristic, not the Public Suffix List, and it will be wrong for suffixes outside that short list — for example k12.ca.us or private suffixes such as github.io.
That formula is labelled as a heuristic because that is what it is. Publishing it as "the root domain formula" without that caveat is how the wrong answer spreads.
Option B — use a tool that has the list. Paste your hostname column into the domain extractor, choose "registrable root domain", and copy the result back. It consults a bundled copy of the Public Suffix List, tells you when a suffix is not recognised, and classifies IP addresses and localhost separately instead of forcing them into a domain column.
For a one-off column that is a thirty-second round trip, and it is right.
Frequently asked questions
Why does my REGEXEXTRACT return #N/A?
REGEXEXTRACT errors when there is no match rather than returning blank — so a cell with no address raises it. Wrapping in IFERROR, as every formula above does, turns that into an empty cell.
Can I use lookbehind in a Sheets pattern?
No. Google Sheets uses the RE2 engine, which deliberately omits backreferences and lookaround so that matching is guaranteed to be fast. Patterns that rely on them will not work here.
How do I get unique domains with a count?
After extracting hostnames, `=UNIQUE(B2:B)` gives the distinct values and `=COUNTIF(B:B, D2)` counts each. The domain extractor does both in one step if you would rather not build it.
Sources
- Google Docs Editors Help — REGEXEXTRACT — Google
- Google Docs Editors Help — SPLIT — Google
- RE2 syntax — the engine Google Sheets uses — Google
- Public Suffix List — Mozilla Foundation