A few suggestions and pieces of advice will help you obtain domain names from a list of URLs using Excel formulas. Two variations of the formula allow you to extract domain names with and without www., regardless of the URL protocol (http, https, ftp, etc., are supported). The solution works in all modern versions of Excel, from 2010 through 2016.

If you're focused on promoting your website (as I am) or conducting professional-level SEO for clients, you frequently need to handle and analyze large lists of URLs: Google Analytics reports on traffic sources, Webmaster tools reports on new links, reports on backlinks to competitors' websites (which contain plenty of valuable insights ;) ), and so on.

Microsoft Excel serves as an excellent tool for managing these lists, whether they contain ten or a million links. It's robust, flexible, extensible, and allows you to send reports directly to clients from an Excel sheet.

"You mention a range from 10 to 1,000,000," you might ask me. "Why is that?" Because clearly, you don't need a tool for fewer than 10 links, and you probably wouldn't need one for over a million either. I'd bet that in such cases, you'd already have custom software tailored to your specific needs. And it would be me reading your articles instead!

When analyzing a list of URLs, common tasks include extracting domain names for further processing, grouping URLs by domain, removing links from already processed domains, comparing and merging two tables by domain names, etc.

Let's consider an example using a snippet of ablebits.com's backlink report generated by Google Webmaster Tools.

Tip: I recommend using ahrefs.com to promptly detect new links to your site and your competitors' websites.

1. Add a "Domain" column to the end of your table. Since we've imported data from a CSV file, our data appears as a simple range. Press Ctrl T to convert it into an Excel table, which is far more convenient to work with.

   

2. In the first cell of the "Domain" column (B2), enter the formula to extract a domain name:

   • Extract the domain with www. if it exists in the URL: <b>=MID(A2,FIND(":",A2,4) 3,FIND("/",A2,9)-FIND(":",A2,4)-3)</b>
   • Exclude www. and get a pure domain name: <b>=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))</b>

   

   The second formula might appear lengthy and complex, but only if you haven't encountered truly long formulas before. It's no coincidence that Microsoft increased the maximum formula length to 8192 characters in newer Excel versions :)

   The advantage is that we don't need to use an extra column or a VBA macro. While creating and using VBA macros isn't as daunting as it seems, check out a great article on how to create and use VBA macros. However, in this specific case, we don't actually need them—using a formula is faster and simpler.

   Note: Technically, www is the 3rd level domain, but with most standard websites, www. is merely an alias of the main domain. Back in the early days of the internet, saying "double u, double u, double u our cool name dot com" on the phone or over the radio was clear enough for everyone to remember where to find you, assuming your cool name wasn't something like www.llanfairpwllgwyngyllgogerychwyrndrobwyll-llantysiliogogogoch.com :)

   You should retain all other third-level domains, otherwise you'll mix up links from different sites, such as those with "co.uk" domains or various accounts on blogspot.com, etc.

3. Since we have a complete table, Excel automatically applies the formula to all cells in the column. Done! We now have a column with extracted domain names.

   

   In the next section, you'll learn how to process a list of URLs based on the Domain column.

   Tip: If you anticipate needing to edit the domain names manually later or copy the results to another Excel worksheet, replace the formula results with values. To do this, follow these steps:

   • Click on any cell in the Domain column and press Ctrl Space to select all the cells in that column.
   • Press Ctrl C to copy the data to the clipboard, then go to the Home tab, click the "Paste" button, and select "Value" from the drop-down menu.

Processing a list of URLs using the Domain name column

Here are a few tips for further processing of the URL list, based on my own experience.

Group URLs by domain

1. Click on any cell in the Domain column.

2. Sort your table by Domain: go to the Data tab and click on the A-Z button.

3. Convert your table back to a range: click on any cell in the table, go to the Design tab, and click the "Convert to Range" button.

4. Go to the Data tab and click the "Subtotal" icon.

5. In the "Subtotal" dialog box, select the following options: At each change in: "Domain" use function Count and Add subtotal to Domain.

6. Click OK. Excel has created an outline of your data on the left-hand side of the screen. There are 3 levels of the outline, and what you see now is the expanded view, or level 3 view. Click number 2 in the upper-left corner to display the final data by domains, and then you can click the plus and minus signs ( / -) to expand/collapse the details for each domain.

   

Highlight the second and all subsequent URLs in the same domain

In the previous section, we demonstrated how to group URLs by domain. Instead of grouping, you can quickly color duplicate entries of the same domain name in your URLs.

For more details, please see how to highlight duplicates in Excel.

Compare your URLs from different tables by domain column

You might have one or several separate Excel worksheets where you keep a list of domain names. Your tables may contain links you don't want to work with, like spam or domains you've already processed. You might also need to maintain a list of domains with interesting links and delete all others.

For example, my task is to color in red all domains that are in my spammer blacklist:

To save time, you can compare your tables to delete unnecessary links. For full details, please read How to compare two Excel columns and delete duplicates.

The best way is to merge two tables by domain name

This is the most advanced method, and the one I personally prefer.

Suppose, you have a separate Excel worksheet with reference data for each domain you've ever worked with. This workbook keeps webmaster

The above is the detailed content of Extract domain names from a URL list in Excel. For more information, please follow other related articles on the PHP Chinese website!