This website uses cookies to customize ads and analyze traffic. If you continue browsing, we'll consider you accept their utilization. More information.
Blog

How to Extract Root Domain from URL in Excel

Excel -

If you manage hundreds of domains and thousands of webpages, you may be as well using a list of webpages with different structures, and that’s the reason why you may need to extract the root domain from each webpage, using an Excel formula. A same webpage may be referred in many ways:

http://danielpinero.com/index.php
http://www.danielpinero.com
http://danielpinero.com
www.danielpinero.com
danielpinero.com

I’ve created a formula to detect only the root domain under many possible variations. This formula cleans the URL from "http://", "www.", subdirectories and files. In case of subdomains, (for example, es.danielpinero.com), the formula keeps intact the first segment (es.)

To insert it in your Excel spreadsheet, you must:

1) Download MyExtract, a special function that allows breaking down a string into many parts. Copy it from MyExtract website. In Excel, go to View > Macros > write "myextract" > Create. Clean the preexistent text, paste the function you’ve just copied y and close the Excel VBA editor. The function won’t be visible as macro, but it has been recorded inside the Excel workbook and will be available as any other standard function.

2) Afterwards, place your URL in cell A1. Then, in cell B1, copy and paste this formula:

=SUBSTITUTE(IF(SUBSTITUTE(SUBSTITUTE(IF(RIGHT(MyExtract(A1;3;"F";"/");
1)="/";LEFT(MyExtract(A1;3;"F";"/");LEN(MyExtract(A1;3;"F";"/"))-1);MyExtract(A1;3;"F";"/"));
"http://";"";1);"www.";"";1)="*";A1;SUBSTITUTE(SUBSTITUTE(IF(RIGHT(MyExtract(A1;3;"F";"/");
1)="/";LEFT(MyExtract(A1;3;"F";"/");LEN(MyExtract(A1;3;"F";"/"))-1);MyExtract(A1;3;"F";"/"));
"http://";"";1);"www.";""));"www.";"")

The root domain has been extracted. You can extend this formula to the remaining cells, or save all this work by downloading it here: formula to extract root domain. If you’re using Excel 2010, remember that you must save this workbook as .xlsm


Related Posts
  • How to Create a Heat Map of Ecuador in Google Geocharts

  • How to Create a Heat Map of Uruguay in Google Geocharts

  • How to Create a Heat Map from Excel

  • How to Create a Heat Map of Chile in Google Geocharts

  • How to Create a Heat Map of Bolivia in Google Geocharts
  • Send Comment
    Name
    E-mail
    Comment

    Comments

    Andy Morley - 2015-04-17
    This only seems to work if the subdomain is www. it doesnt work for xyz. or abc. for example... In that case its a lot faster to simply find and replace www. with &quotblank&quot. Andy

    Eric - 2013-10-12
    Hi Daniel,I've tried it, but it doesn't get rid of subdomains, which is exactly what I need. Not sure if I'm doing something wrong or the formula is not doing it.Any help would be appreciated.Thanks for sharing this! :)

    Daniel - 2013-10-13
    Hi Eric. Can you send the Excel file to me, so I can check it out? Thanks.

    Yann - 2013-11-06
    Hi, I cannot find a way to keep only the host part. I need to remove the subdomain, so for es.danielpinero.com I actually need to return danielpinero.com Any idea ?