If you are managing an SEO campaign, building a backlink database, or analyzing a competitor’s web traffic, you likely have a Google Sheet containing thousands of raw, ugly URLs.
Your list might look like a chaotic mess of different formats:
https://www.apple.com/macbook-pro/http://microsoft.com/en-us/windows/https://blog.google.com/products/
If you want to run a pivot table to see which company you have linked to the most, you cannot use these raw URLs, because every single link is technically unique. You need to strip away the https://, the www., and the trailing subdirectories (/macbook-pro/) to extract the pure, raw root domain (e.g., apple.com). Because URLs are completely unpredictable in length, standard LEFT() and RIGHT() formulas will fail. You must use Regular Expressions.
The REGEXEXTRACT Function
Google Sheets includes the incredibly powerful REGEXEXTRACT function, which allows you to use complex pattern-matching algorithms to pull specific text out of a chaotic string.
=REGEXEXTRACT(text, regular_expression)
Step 1: Write the Extraction Formula
Assume your messy URLs are listed in Column A, starting in cell A2.
- Click on cell B2 (or any empty adjacent column).
- Paste the following massive formula exactly as written:
=REGEXEXTRACT(A2, "^(?:https?:\/\/)?(?:www\.)?([^\/]+)") - Press Enter.
- Drag the formula down to apply it to your entire dataset.
How the Regex Witchcraft Works
Regular expressions look like absolute gibberish, but they are highly logical mathematical instructions. Let’s break down what this specific code is commanding Google Sheets to do:
^: Start reading from the very beginning of the URL in cell A2.(?:https?:\/\/)?: Look for “http://” or “https://”. The?at the end means “If you find it, ignore it. If you don’t find it, that’s fine too.”(?:www\.)?: Look for “www.”. Again, the?means ignore it if it exists.([^\/]+): This is the payload. This explicitly commands the formula to grab every single character it sees (the actual domain name likeapple.com), but it must instantly stop grabbing the very second it hits the first forward slash (/). This guarantees it cuts off all the subdirectories and messy file paths.
The Result
Column B will instantly transform that chaotic list of URLs into perfectly clean, uniform root domains (apple.com, microsoft.com, blog.google.com). You can now select Column B and generate a pristine Pivot Table to accurately count your backlink distribution without any formatting errors.