Nov
8

How to quickly extract domain names from URLs in Excel

A few tips and pieces of advice will help you get domain names from a list of URLs using Excel formulas. Two variations of the formula let you extract the 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.

Formula to get domain names from a URL list in Excel

If you are concerned with promoting your web-site (like I am) or doing SEO at the professional level promoting clients' web-sites for money, you often have to process and analyze huge lists of URLs: Google Analytics reports on traffic acquisition, Webmaster tools reports on new links, reports on backlinks to your competitors' web-sites (which do contain a great lot of interesting facts ;) ) and so on, and so forth.

To process such lists, from ten to a million links, Microsoft Excel makes an ideal tool. It is powerful, agile, extendable, and lets you send a report to your client directly from an Excel sheet.

"Why is it this range, from 10 to 1,000,000?" you may ask me. Because you definitely don't need a tool to process fewer than 10 links; and you will hardly need any if you have over a million inbound links. I'd wager that in this case you'd already had some custom software developed especially for you, with a business logic specifically tailored for your needs. And it would be me who would peruse your articles and not the other way round :)

When analyzing a list of URLs, you often need to perform the following tasks: get domain names for further processing, group URLs by domain, remove links from already processed domains, compare and merge two tables by domain names etc.

5 easy steps to extract domain names from the list of URLs

As an example, let's take a snippet of ablebits.com's backlinks report generated by Google Webmaster Tools.

URLs from a snippet of ablebits.com's backlinks report

Tip: I'd recommend using ahrefs.com to timely spot new links to your own site and your competitors' web-sites.
  1. Add the "Domain" column to the end of your table.

    We have exported the data from a CSV file, which is why in terms of Excel our data are in a simple range. Press Ctrl + T to convert them to an Excel table because it is far more convenient to work with.
    Press CTRL+T to convert URLs list to an Excel table

  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 is present in a URL:
      =MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3)
    • Omit www. and get a pure 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))

    Formula to extract a domain name from URL

    The second formula may seem too long and complex, but only if you didn't see truly long formulas. It's not without reason that Microsoft has increased the maximum length of formulas up to 8192 characters in new versions of Excel :)

    The good thing is that we don't have to use either an additional column or VBA macro. In fact, using VBA macros to automate your Excel tasks is not so difficult as it may seem, see a very good article - Tutorial with Excel examples about Macros. But in this particular case, we do not actually need them, it's quicker and easier to go with a formula.

    Note: Technically, www is the 3rd level domain, though with all normal web-sites www. is just an alias of the primary domain. In the early days of the Internet, you could say "double u, double u, double u our cool name dot com" on the phone or in a radio advert, and everyone perfectly understood and remembered where to look for you, of course unless your coolname was http://www.llanfairpwllgwyngyllgogerychwyrndrobwyll-llantysiliogogogoch.com/ :)

    You need to leave all other domain names of the 3rd level, otherwise you would mess up links from different sites, e.g. with "co.uk" domain or from different accounts on blogspot.com etc.

  3. Since we have a full-fledged table, Excel automatically copies the formula across all cells in the column.

    Done! We have a column with extracted domain names.
    A column with extracted domain names

    In the next section you will learn how you can process a list of URLs based on the Domain column.

    Tip: If you may need to edit the domain names manually at a later time or copy the results to another Excel worksheet, replace the formula results with values. To do this, proceed with the following 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 Clipboard, then go to the Home tab, click the "Paste" button and select "Value" from the drop-down menu.
      Replace the formula results with values

Processing a list of URLs using the Domain name column

Here you will find a few tips on further processing of the URL list, from 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 the 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.
    Add subtotal to your table
  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 hand corner to display the final data by domains, and then you can click the plus and minus signs (+ / -) in order to expand / collapse the details for each domain.
    Click the plus and minus signs ( + -) in order to expand / collapse the details for each domain.

Highlight the second and all subsequent URLs in the same domain

In our previous section we showed 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 automatically highlight duplicates in Excel.
Highlight the second and subsequent mentions of a domain name in your URL table

Compare your URLs from different tables by domain column

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

For example, my task is to color in red all domains that are in my spammer blacklist:
Compare two tables and highlight duplicates

Not to waste much time, you can compare your tables to delete the unnecessary links. Please read How to compare two columns in Excel and delete duplicates (highlight, color, move)

The best way is to merge two tables by domain name

This is the most advanced way and the one I personally prefer.

Suppose, you have a separate Excel worksheet with reference data for each domain you ever worked with. This workbook keeps webmaster contacts for link exchange and the date when your website was mentioned in this domain. There can also be types/subtypes of websites and a separate column with your comments like on the screenshot below.
Domain lookup table

As soon as you get a new list of links you can match two tables by domain name and merge the information from the domain lookup table and your new URLs sheet in just two minutes.

As a result you will get the domain name as well as the website category and your comments. This will let you see the URLs from the list you need to delete and those you need to process.

Match two tables by domain name and merge data:

  1. Download and install the latest version of Merge Tables Wizard for Microsoft Excel

    This nifty tool will match and merge two Excel 2013-2003 worksheets in a flash. You can use one or several columns as the unique identifier, update existing columns in the master worksheet or add new from the lookup table. Feel free to read more about Merge Tables Wizard on our website.

  2. Open your URLs list in Excel and extract domain names as described above.
  3. Select any cell in your table. Then go to the Ablebits Data tab and click on the Merge Two Tables icon to run the add-in.
    Run the Merge Tables Wizard for Excel
  4. Press the Next button twice and select your worksheet with the domains information as the Lookup Table.
    Select your worksheet with the domains information as the Lookup Table
  5. Tick the checkbox next to Domain to identify it as the matching column.
     Tick the checkbox next to Domain to identify it as the matching column.
  6. Select what information about the domain you want to add to the URLs list and click Next.
    Select what information about the domain you want to add to the URLs list
  7. Press the Finish button. When the processing is over, the add-in will show you a message with the details of the merge.
    The add-in will show you a message with the details of the merge

Just a few seconds - and you get all information about each domain name at a glance.
Two tables merged - you get all information about each domain name at a glance.

You can download Merge Tables Wizard for Excel, run it on your data and see how useful it can be.

If you are interested to get a free add-in for extracting domain names and subfolders of the root domain (.com, .edu, .us etc.) from the URL list, simply drop us a comment. When doing this, please specify your Excel version, e.g. Excel 2010 64-bit, and enter your email address in the corresponding field (do not worry, it won't be displayed publically). If we have a decent number of votes, we will create such and add-in and I let you know. Thank you in advance!

23 Responses to "How to quickly extract domain names from URLs in Excel"

  1. Imam Suharjo says:

    Glad you found it useful Alexander and also for the tips for other all readers

  2. Muhammad Shoeb says:

    Dude, this is complete madness of the formulaes of Excel. Excellent tutorial and very much helpful.. Keep it up!!!

  3. Neeshu says:

    can you please make a video of this reading the entire article is a bit time consuming :( specially when you are in the office.

  4. Vishal Gohel says:

    Thank you very much :)

  5. Sam Barnes says:

    Thanks so much for this info.

    Been using it a lot recently as I've been working with lots of link exports.

    It's been a real time saver.

    • Alexander says:

      Thank you for your feedback, Sam. If there are any other topics that may be of interest to you, please let me know and I will try to cover them in the next posts.

  6. Ben says:

    That's great thanks.

    It seems to be missing one option if a URL has no / at the end it gives an error.

    How could I fix the formula to include this?

    Thanks

  7. Wella says:

    I'm using another method on how to extract the domain from a URL. Here's how I do it: =left(A2,search("/",A2,9))

    where A2 is the cell where the URL is located.

    The formula basically searches for the "/" after the 9th character in cell A2 and cuts everything out when it returns.

  8. vovuong says:

    Your formula works well for this URL type http://dichvudangtin.noodesign.net

  9. Pedro says:

    Thank You !! It works!

    For spanish speakers how to extract in spanish excel:
    =SI(ESERROR(HALLAR("//www.";A2)); EXTRAE(A2;HALLAR(":";A2;4)+3;HALLAR("/";A2;9)-HALLAR(":";A2;4)-3); EXTRAE(A2;HALLAR(":";A2;4)+7;HALLAR("/";A2;9)-HALLAR(":";A2;4)-7))

    For SEO purposes: Como sacar el dominio de una URL:
    =SI(ESERROR(HALLAR("//www.";A2)); EXTRAE(A2;HALLAR(":";A2;4)+3;HALLAR("/";A2;9)-HALLAR(":";A2;4)-3); EXTRAE(A2;HALLAR(":";A2;4)+7;HALLAR("/";A2;9)-HALLAR(":";A2;4)-7))

  10. Roger Wicki says:

    This one is compact and good but not sufficient for my needs. We have URLs in as good as every possible (and impossible) format. We have URLs with protocol, without protocol, including www, excluding it, already exist of only the domain, exist of only domain and location, includes parameters and so on. I think you get the point.

    One example would be "1und1.de/index.php?page=platin_umetz"
    This is a very common format of URLs we have as data.

    This is the one I made for myself: (Requires a table with a column named Website (Insert > Table))
    =LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@Website];"www.";"");"http://";"");"https://";"");IFERROR(FIND("/";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@Website];"www.";"");"http://";"");"https://";""))-1;LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@Website];"www.";"");"http://";"");"https://";""))))

    If you find some URL that does not work with this formula, please let me know. We can still refine this further.

  11. Kashif says:

    Nice tutorial , i am not good in excel and need a little help if you can ...

    i have a domains list in excel , like :

    abc.com
    abc.net
    abc.us
    abc.ca

    etc etc ... What i actually need is domains extensions in next tab

    i need only extensions in a seperate tab , so that i can easily filter all .coms etc ..

    is that possible ?

  12. Sonu says:

    We can use this formula too.

    =MID(D3,FIND(".",D3)+1,RIGHT(FIND(".com",D3),LEN(D3))-8)

  13. Adhiraj says:

    Hi,
    In the IF(ISERROR) formula mentioned above how do we return a null value if the reference cell is blank.

  14. Richard says:

    I tried following your method, but couldnt get it. Then I found this: http://tripleseo.com/microsoft-excel-tips-for-seo-extracting-domains-from-urls/

  15. ikomrad says:

    How would you extract the domain name from the fqdn if it isn't part of the url.

    it

    fileserver.mydomain.com
    hostname: fileserver
    Domain: mydomain.com

  16. Tik. says:

    Thanks a lot for sharing how to extract a domain with Excel.

  17. Dazy Parker says:

    Amazing article

  18. Jim says:

    Pieced this together to work with domains that end in slashes or not and then also strip the final slash if needed...

    =IF(RIGHT(IF(ISNUMBER(SEARCH("/",AN2,9)),LEFT(AN2,SEARCH("/",AN2,9)),AN2),(LEN(IF(ISNUMBER(SEARCH("/",AN2,9)),LEFT(AN2,SEARCH("/",AN2,9)),AN2))-(LEN(IF(ISNUMBER(SEARCH("/",AN2,9)),LEFT(AN2,SEARCH("/",AN2,9)),AN2))-1)))="/",LEFT(IF(ISNUMBER(SEARCH("/",AN2,9)),LEFT(AN2,SEARCH("/",AN2,9)),AN2),(LEN(IF(ISNUMBER(SEARCH("/",AN2,9)),LEFT(AN2,SEARCH("/",AN2,9)),AN2))-1)),IF(ISNUMBER(SEARCH("/",AN2,9)),LEFT(AN2,SEARCH("/",AN2,9)),AN2))

  19. smsharie says:

    Hello ,

    This is absolutely a great site , I appreciate all the effort .

    I need a help now, Is there a macro to extract the exact Company Name from web, if we have a list of company domains as excel input please (especially when the domains look weird )?

    Any help would be much appreciated .

    Warm regards,
    smsharie

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard