URL Tools Add-In for Excel

Aug. 24, 2010 | by James Taylor

Here in the Natural Search department we use Excel. A lot. And frequently we are taking data from the web (such as from the rather good Open Site Explorer) and manhandling it in order to make more sense of it. As part of this process, it’s often helpful to be able to quickly pull out the domain portion of a URL. For example, let’s say you have a list of URLs like this:

  • http://www.example.com/
  • http://www.icrossing.co.uk/file1.html
  • http://www.icrossing.co.uk/file2.html
  • http://example.com/another_file.html
  • http://www.acompletelydifferentdomain.com/

You might find yourself in a position where want to take this list and reduce it to just the domain portions like this:

  • example.com
  • icrossing.co.uk
  • icrossing.co.uk
  • example.com
  • acompletelydifferentdomain.com

It’s possible to use an Excel formula to do this. Here’s one I found by searching:

=IF(LEFT(LEFT(SUBSTITUTE(A1,”http://”,”"),FIND(“/”,SUBSTITUTE(A1,”http://”,”")&”/”)-1),4)=”www.”,MID(LEFT(SUBSTITUTE(A1,”http://”,”"),FIND(“/”,SUBSTITUTE(A1,”http://”,”")&”/”)-1),5,256),LEFT(SUBSTITUTE(A1,”http://”,”"),FIND(“/”,SUBSTITUTE(A1,”http://”,”")&”/”)-1))

This is fine, but as you can see the formula is rather long and combining this with any further functions would be a real headache. To try to solve this, I wrote an add-In for Excel that adds a handy function to perform this step (along with a few others).

Download the URL Tools Excel Add-in Here

To use the Add-in, you’ll need to save it somewhere on your computer (we use ‘C:\Program Files\URL Tools\’ by convention, which helps when sharing workbooks) and install the add-in via the Excel Options menu as follows:

1. Click on the Office Button in Excel

2. Select ‘Excel Options’ from the bottom of the menu

3. Select ‘Add-Ins’ from the left hand menu in the resulting dialog box

4. Click ‘Go’ where it says ‘Manage Excel Add-ins’

5. Click ‘Browse’ and browse to wherever you saved the Add-in file

6 Click ‘OK‘ in the appropriate places to return to Excel

Hopefully, you will now have the Add-in installed. To test this, do the following: Put a URL in cell A1. Any URL, but try something long like http://en.wikipedia.org/wiki/Noel_edmonds. Then, in cell A2 type ‘=wwwsubdomain(A1)’. All being well, this formula should return ‘en.wikipedia.org’. If so, celebrate, otherwise go back and check the installation steps again.

There are a number of other functions included in the Add-in that I will summarise below. There are a couple of options for handling the domain extraction outlined above, but they are subtly different. If in doubt, you probably just want to use ‘=wwwsubdomain’.

  • subdomain() – returns the domain part of a URL including any subdomains e.g ‘http://www.example.com/index.htm’ becomes ‘www.example.com’
  • nowww() – removes ‘www.’ from the start of a URL (note that www must be at the very beginning of the URL)
  • wwwsubdomain() – basically, a combination of the above, equivalent to ‘nowww(subdomain(A1))’. This is what you want to use most of the time!
  • urlencode() – Encodes a URL string (try it on a string like ‘Hello World!’)
  • urldecode() – Un-encodes a URL string (try it on a string like ‘Hello%20World%21’)
  • tld() – Returns the TLD of a URL (not 100% perfect but pretty good)
  • geturl() – Extracts the URL from a Hyperlink

I hope you find this useful. It’s very much a work in progress so please let me have your feedback or questions in the comments section below.

Be Sociable, Share!

    Comments (50)

    • Joerg Reitenbach

      Hi,
      is it possible to ad new TLDs ?
      I'm missing
      .at, .lv, .by ....
      greeting JörgAug 21, 2014 01:36 pm

    • Five Essential Excel Tools and Tips for SEOs | Social Media Marketing 24/7

      […] the power and versatility of SEO Tools comes a much more modest add-in for Excel. URL Tools is an add-in by James Taylor of iCrossing that I’ve found invaluable over the last couple of […]Jun 30, 2014 03:09 pm

    • Five Essential Excel Tools and Tips for SEOs

      […] the power and versatility of SEO Tools comes a much more modest add-in for Excel. URL Tools is an add-in by James Taylor of iCrossing that I've found invaluable over the last couple of […]Jun 30, 2014 02:58 pm

    • Dreamwaverのテンプレートについての質問です。 《テンプレートファイル"xxxxxx.dw̷

      […] 2013、URL Tools for Excel、SEO Tools for Excel […]May 10, 2014 01:07 am

    • ネット環境がなくてもメッセージのやり取りが可能なチャットアプリ「FireChat」を使ってみました | 新潟でワードプレスのホームページ制作はパーソン2パーソナルズ

      […] 2013、URL Tools for Excel、SEO Tools for Excel […]Apr 18, 2014 05:32 pm

    • 指1本の直感的な操作でデザイン性が高いホームページが作れる無料アプリ「ロリポタッチ」を使ってみました | 新潟でワードプレスのホームページ制作はパーソン2パーソナルズ

      [...] 2013、URL Tools for Excel、SEO Tools for Excel [...]Mar 15, 2014 08:02 pm

    • Elijah

      Hi James!

      I hope this can also remove domains from a url and leave only url strings. :) I can't find that anywhere. This is such a great help! Thank you so much!Dec 5, 2013 07:04 pm

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | DIY SEO/SMM Plans

      [...] custom filters which are only available in the paid version) One of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Nov 6, 2013 11:11 am

    • Eric

      Hey James,

      Thanks for sharing this great tools. I'm having trouble stripping the subdomains from the domains though. Is there any way to achieve this?

      Example:
      wwwdb.eursoparl.eu.int -> to -> eursoparl.eu.int
      lakota.cleara.net -> to -> cleara.net
      open.gov.uk -> to -> open.gov.uk
      noticiario.baeguaperu.com -> to -> baeguaperu.comNov 3, 2013 05:21 pm

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | Directory Net

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 30, 2013 09:43 pm

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | SEOPTI

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 30, 2013 06:56 pm

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | Search Engine Optimization & Internet Marketing (SEO & SEM) Blog

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 30, 2013 06:42 am

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | Internet Marketing

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 30, 2013 05:06 am

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | Meet Me SEO

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 30, 2013 03:43 am

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog |

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 30, 2013 02:59 am

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | Artman's

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 30, 2013 02:01 am

    • Kilobuzz | Quick Guide to Scaling Your Authorship Testing with Screaming Frog « KiloBuzz

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 30, 2013 01:54 am

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | Sudbury SEO, PPC & Web Design Services

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 30, 2013 01:31 am

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog - World Digital

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 30, 2013 12:55 am

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | No Exp Required

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 29, 2013 07:33 pm

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | Breaking Internet Marketing News

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 29, 2013 04:24 pm

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | Global SEO Solution | SEO Services in India |Global SEO Solution | SEO Services in India |

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 29, 2013 02:23 pm

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 29, 2013 01:42 pm

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog - Ecnomy.com

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 29, 2013 12:18 pm

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | Traffic Conversion

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 29, 2013 10:19 am

    • Quick Guide to Scaling Your Authorship Testing with Screaming Frog | Benson SEO Services Singapore

      [...] of the following: Excel 2013, URL Tools for Excel, or SEO Tools for Excel (any of these three will allow us to encode URLs inside of Excel with a [...]Oct 29, 2013 09:59 am

    • SEO Tools for Excel | The Content Authority

      [...] The SEO tools add-on mentioned above has a URL extraction tool in it, but you can also check out this one. It allows you to extract the root domain name from URLs in a spreadsheet. This comes in handy when you have a ton of URLs, but just want to see the root domains. You can download the add-on here, and learn more about how to install it here. [...]Oct 12, 2013 09:55 am

    • Operation: Finding & Removing Unnatural Links After Google Site Penalty or Manual Action

      [...] Google Webmaster Tools, pop them into Excel. I assume you have SEO Tools for Excel (I also have URL Tools [...]Apr 29, 2013 09:34 am

    • AllSearch52

      Excellent tool thanks for sharing. This is really handy for quick seo analysis.Jan 14, 2013 11:22 pm

    • MozCon for non SEOs: Why you need to be at the next one! | oblongpixel.com

      [...] This URL Tools Addin makes it easy to perform common actions on URLs in Excel like concatenating down to the domain level. http://connect.icrossing.co.uk/url-tools-addin-excel_5458 [...]Aug 14, 2012 04:06 pm

    • Ilja Z.

      Thanks for this amazing tool James!

      Unfortunately I encountered some problems with different Excel versions - in 2010 everything worked just fine, whereas in 2007 nothing happened. You maybe want to try Niels Bosma's SeoTools which provide a great amount of functionalities. If you are looking for URL formatting, check out the =UrlProperty() function here: http://nielsbosma.se/projects/seotools/functions/urlproperty/Aug 8, 2012 09:57 pm

    • How to Do Your Own Backlinks Risk Assessment « Miko Jose

      [...] the unique root domains only. (A tutorial on how to extract unique root domains here. The excel add-in can also be downloaded on the [...]May 14, 2012 10:31 pm

    • Mike Shaw

      Excellent - a great help, thanks.Apr 17, 2012 02:37 pm

    • konstantin

      Super !!!! 1000 thannks !!!Mar 5, 2012 10:49 pm

    • Rusty Shack

      Nice work. Some TLDs don't work, as you know. ICANN adds new ones occasionally, but it shouldn't be hard to get them all. After fixing that, I'd add a function to extract the domain name without the subdomain. That's often handy when you want to sort a list and keep all the ones with same domain together (despite various subdomains). Found ASAP Utilities handy, too, but I like your approach of adding formula functions much better.Feb 28, 2012 09:41 am

    • Jon

      Thanks very much for this, works a treat. Really useful.Feb 23, 2012 04:52 pm

    • Nigel

      Great Add On! It does exactly as advertised. Thx for saving me a ton of time guys.Nov 7, 2011 09:05 pm

    • Riel

      Thank you for this wonderful, amazing add-on. It extracted all the TLDs on my long list perfectly. Wow! That makes me so happy. A million blessings to you!Mar 24, 2011 02:04 am

    • James Taylor

      =subdomain(A1) will leave the www. portion of the domain intact if it exists. Not sure if that's what you mean, though.

      It started as a way to just grab the domain part of a URL but I added more functions as my colleagues requested features, usually for specific tasks or problems they needed to solve such as matching a list of encoded URLs to unencoded URLs.Mar 4, 2011 01:05 pm

    • Ed

      @James Taylor

      And I guess it puts back the www. aswell?
      Anyhow - there's more use to the toolset than just the stripping to root function.

      Where did you get the inspiration to build this suite?Mar 4, 2011 12:59 pm

    • James Taylor

      Hi Ed, I don't agree that this is long winded. The whole point is that it is not! Once the add-in is installed it's just a case of using the built in function(s). eg =wwwsubdomain(A1) gets the same result as your find/replace method above.Mar 4, 2011 12:54 pm

    • Ed

      It seems a long winded way to do: CTRL+F 'http:///www.' --->Replace with nothing

      Then...

      =LEFT(A1,FIND("/",A1)-1)

      ...Dedupe

      ...Concatanate to rebuildMar 3, 2011 09:59 pm

    • Magico

      Fair enough. I have 2007 at the moment and it doesn't autocomplete. Boo to old software versions.

      Helena, I've got Excel 2007 and it works fine. What steps are you following?Feb 11, 2011 02:39 pm

    • James Taylor

      Hello Magico! I think the autocompletion depends on which version of Excel you are using. It autocompletes the function names in my version of Excel (2010) but didn't in whatever version I was using before.Feb 11, 2011 02:15 pm

    • James Taylor

      Sorry to hear that, Helena. If you can give me any more details I can try to help you. Are you getting an error message at all? When you download the file does it save with the correct extension (.xlam)?Feb 11, 2011 02:13 pm

    • Magico

      Hey James. Have you updated this since version 1.02?
      It would be great if the functions came up in excel's autocomplete feature. Not sure whether that is possible though...

      Other than that, keep up the good work ;)Feb 10, 2011 03:10 pm

    • Helena

      Love the idea of the URL Tools. But I can't install in my Excel 2007.  Followed the exact installation steps but no success. Does anyone have the same problem or maybe a solution?Feb 10, 2011 09:18 am

    • James Taylor

      Yeah, sure! Can you list the TLDs you would like added below if it's not too many?Jan 31, 2011 06:10 pm

    • Windfery

      This tool is fantastic! Any chance of getting some TLDs added to its functionality?Jan 31, 2011 04:35 am

    • James Taylor

      That's odd. I have just tested it and it's working for me. For example, if you have a hyperlink in a cell in A1 then =GetURL(A1) should return the link URL. Can you let me know the exact error message and which version of Excel you are using?Oct 18, 2010 05:10 pm

    • Chris

      Hi James,

      For some reason the =GetURL() function returns an error saying that it doesn't take any parameters - any chance that this is still WIP?

      Thanks for your efforts,

      ChrisOct 18, 2010 03:17 pm

    • James Taylor

      Hi Keith, after some delay I have updated the tool to work with .ca domains. Glad you find it useful!Oct 6, 2010 04:47 pm

    • Keith McCartan

      Thanks for URL tools v1.01. Very usefull and works well.

      I did notice that the tld() function does not work properly for .ca domains. Otherwise works fine.Sep 10, 2010 10:59 pm

     
    Please note: the opinions expressed in this post represent the views of the individual, not necessarily those of iCrossing.

    Post a comment

    SUBSCRIBE