Contents
A few tricks and tips will help you extract domain names from a list of URLs using Excel formulas. Two variants of the formula allow you to extract domain names with WWW or without, regardless of the URL protocol (http, https, ftp and others are supported). The solution works in all modern versions of Excel from 2003 to 2013.
If you care about promoting your site (as I do) or performing professional SEO for your clients’ sites for money, you will often be faced with the task of processing and analyzing huge lists of URLs: Google Analytics provides traffic reports, webmaster tools report new links, about backlinks to your competitors’ sites (which contain a lot of interesting facts) and so on.
To process such lists from a dozen to a million links, Microsoft Excel becomes the perfect tool. It’s powerful, flexible, extensible and allows you to send reports to clients directly from an Excel sheet.
Why do we call the range 10 to 1000000? – you ask me. Because you definitely don’t need a tool to process less than 10 links, and you probably don’t need one if you have more than a million incoming links. I bet that in this case you already have some software designed specifically for you and adapted specifically to the needs of your business. Plus, in this case, I would study your articles, and not vice versa 🙂
When analyzing a list of URLs, you often face the following tasks: isolate domain names for further processing, group URLs by domains, remove links from already processed domains, compare and merge two tables by domain names, and so on.
5 Easy Steps to Extract Domain Names from a List of URLs
As an example, let’s take a snippet of the backlinks report generated for ablebits.com by Google Webmaster Tools.
Tip: I recommend using the ahrefs.com service to identify new links for your own site and those of your competitors in a timely manner.
- Add a column Domain to the end of your table. We exported data from a file CSV, so now they occupy a simple range. Click Ctrl + Tto convert it to an Excel spreadsheet, as it will be much more convenient to work with.
- In the first cell of the column Domain (B2) enter the formula to extract the domain name:
- We extract the domain with “WWW.” if it is present in the URL:
=MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3)=ПСТР(A2;НАЙТИ(":";A2;4)+3;НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-3) - Skip “WWW.” and extract the clean domain name:
=IF(ISERROR(FIND("//www.",A2)),MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3), MID(A2,FIND(":",A2,4)+7,FIND("/",A2,9)-FIND(":",A2,4)-7))=ЕСЛИ(ЕОШИБКА(НАЙТИ("//www.";A2));ПСТР(A2;НАЙТИ(":";A2;4)+3;НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-3);ПСТР(A2;НАЙТИ(":";A2;4)+7;НАЙТИ("/";A2;9)-НАЙТИ(":";A2;4)-7))
The second formula may seem too long and complicated, but only if you haven’t seen really long formulas. Microsoft has not without reason increased the maximum length of formulas to 8192 characters for new versions of Excel 🙂
The upside is that we don’t need a helper column or a VBA macro. In fact, using VBA macros to automate tasks in Excel is not as difficult as it might seem, read more about it in an excellent article – Excel Macro Tutorial with Examples. But in this case, we don’t really need them: it’s quicker and easier to do it with a formula.
- We extract the domain with “WWW.” if it is present in the URL:
Note: Technically, “WWW” is a third-level domain, although in all normal sites, “WWW.” used as a prefix to a first-level domain name. In the early days of the internet, you could say over the phone or in a radio ad: Ve-Ve-Ve, Our_Cool_Site, Tochka Kom, – and everyone perfectly understood and remembered where you can be found … of course, if the name of your cool site was not http://www.llanfairpwllgwyngyllgogerychwyrndrobwyll-llantysiliogogogoch.com/ 🙂
You need to leave all other level 3 domain names, otherwise you can mix links from different sites, for example, from the CO.UK domain or from different accounts on BLOGSPOT.COM and so on.
- Since we have a full table, Excel automatically copies the formula to all rows of the column.
Ready! We have a column with extracted domain names.
In the next part, you will learn how you can process a list of URLs using a column Domain.
Tip: If you need to manually edit the domain names or copy the results to another Excel sheet, replace the formulas with their values. To do this, follow these steps:
- Click on any cell in a column Domain and press Ctrl+Space (Space) to select all cells in that column.
- Press Ctrl + Cto copy the data to the clipboard, then open the tab Home (Home), click the dropdown button paste (Insert) and select an option Value (Values).
Processing a list of URLs by a column with a domain name
In this part of the article, you will find some tips from my own experience for further processing the list of URLs.
Grouping URLs by Domain Name
- Click any cell in a column Domain.
- Sort table by column Domain: on tab Data (data) click AZ (AND I).
- Convert table back to range: click any table cell, open tab Design (Constructor) and click Convert to the range (Convert to range).
- On the Advanced tab Data (Data) click icon Subtotal (Subtotal).
- In the dialog box Subtotal (Subtotals) set the following parameters: in the line At each change in (At each change in) – the value of “Domain”; in line Use function (Operation) – value “Count” (Number); in line Add subtotal to (Add totals by) – value “Domain”.
- Click OK.
Excel will create your data structure on the left side of the screen. The structure in our case consists of 3 levels. What you see in the screenshot below is an exploded or level 3 view. Click on the number 2 in the upper left corner of the screen to display the domain totals. You can then click the plus and minus signs (+/-) to expand/collapse the details for each domain.
Highlight the second and all subsequent URLs in the common domain
In the previous part of the article, we showed how to group URLs by domain. Instead of grouping, you can quickly colorize duplicate entries in your URLs.
Read more about this method in the article. How to automatically highlight duplicates in Excel.
Compare URLs from different tables by domain name column
You may have one or more separate Excel sheets that contain a list of domain names. Your tables may contain links you don’t want to mess with, such as spam or domains you’ve already processed. You may need to keep the list of domains with interesting links and delete the rest.
For example, my task is to highlight in red all the domains that are on my blacklist of spammers.
Without wasting much time, you can compare your tables and remove unnecessary links. Read about it in the article How to compare two columns and remove duplicates in Excel.