Learn how to extract a domain from a URL using this simple Google Sheets formula. (It also works with Excel)
If you do SEO, at some point you are going to have to wrangle some URLs to get what you need. And if you are anything like me, these URLs are probably going to be in a spreadsheet.
Working with a list of backlinks in a sheet happens daily for me. Sometimes it is useful to extract domains from the URLs to make data easier to read, organise your data into top domains or just to tidy up your work.
With Google Sheets, this is very easy to do:
How to extract domain from URL
If you want to extract the domain name from a URL, you can use a formula that uses the LEFT and FIND functions.
Here’s how to get the domain name from a URL:
Formula to get domain name from URL
When it comes to URL extraction, you’ve got two options for how you want the output:
WITH trailing slash:
=LEFT(A2,FIND("/",A2,9))
WITHOUT trailing slash:
=LEFT(A2,FIND("/",A2,9)-1)
*in these formulas, the URL you want to wrangle is in cell A2 👍
Example of formula in action
In the example below, we have a list of totally genuine URLs in column A. In column B we have our formula that strips out the page path, leaving just the domain.
When I’ve used this on backlink data pulled from Ahrefs, there can be thousands of individual links to tidy up, and this simple formula can quickly do the heavy lifting for me.
How it works
If you care at all want to understand how this formula works, here’s a quick breakdown:
- A2 contains the URL
- The formula extracts characters from the URL starting from the LEFT.
- It uses the FIND function to figure out how many characters to extract.
- FIND locates the “/” character in the URL (beginning at the 9th character).
- URLs start with a protocol (http://, https://, ftp:// etc)
- This protocol is skipped by beginning at the 9th character.
- The FIND function will return the 3rd occurance of the slash (“/”) aka the first time after the double slash in the protocol at the start of the URL.
So the formula will basically FIND the number of characters after the slash, and then the LEFT function will extract that number from the URL 🙌
The result is the domain name extracted from the URL + a trailing slash. That formula again:
=LEFT(A2,FIND("/",A2,9))
If you want it without a trailing slash, just add -1 to the formula:
=LEFT(A2,FIND("/",B4,9)-1)
Summary
There you have it, how to quickly extract a domain from URL in Google Sheets. If you want to find out more about the functions used in this spreadsheet, go check out the official Google Sheets support docs:
Happy extracting!