Pinterest Stumbleupon Whatsapp
Ads by Google

show url links excelExcel is an awesome data management tool. I use it a lot to store and organize the information I need to deal with on a daily basis. As I am working online, most of the time this information contains multiples URLs linking to web sources.

Excel provides for plenty of flexibility to geeky, macros-savvy people. I am not one of them and creating some formulas or macros isn’t an easy task for me. If you are like me, you’ll find my tips on extracting and activating multiple hyperlinks in Excel quite handy!

1. Extract URLs

There’s a great tool that would make a lot of Excel tasks (including extracting hyperlinks from the whole list of linked cells) clearer and easier to implement – ASAP Utilities. It is easy to install, just download and run its free version.

Note: You might have to tweak your Excel security settings a bit before being able to use the tool. The tool offers step-by-step prompts, so you are unlikely to have any problems. First, to make sure the addin is going to work, navigate:

File -> Options -> Trust Center -> Trust Center Settings

There:

Ads by Google
  • Make sure macros are enabled (in “Macro Settings”)
  • Make sure the addins are NOT disabled (in “Add-ins”)

show url links excel

After having it installed, you will get a new submenu with the handy “Web” section. Clicking on it will discover some very cool opportunities for working with content you copy-paste from the web.

So imagine you have copied-pasted the whole lots of linked words from the web and need to see the full URLs now – all you need to do is:

  • Navigate: ASAP Utilities -> Web -> Extract Hyperlinks
  • Select where the full URLs should be extracted:

show url links excel

Done!

how to convert text hyperlink to url in excel

2. Activate Hyperlinks

Let’s say you have the list of plain-text URLs (like in the above screenshot in column B) and want to make them all clickable. One way is to go through the list and double click each cell – this will activate each link, but this can take time (especially with long lists of URLs).

With ASAP Utilities, you can do that with (almost) one click of a mouse. Just select the column with your plain text URLs, go ASAP Utilities -> Web -> Activate hyperlinks and select the format and type of the hyperlinks:

how to convert text hyperlink to url in excel

Done!

how to extract url from hyperlink in excel

Note: The “Web” section of Asap Utilities also allows you to change the format of your links to =HYPERLINK() formulas. Workbooks in Excel can become slow when they contain too many cells with hyperlinks. The =HYPERLINK() formula should let you work with huge workbooks easier.

3. Extract Domains and Top Level Domains from Hyperlinks

Imagine you have a list of URLs or linked cells in Excel and you need to sort the table by domain or top level domain (or build some stats). For that, you will need this URL tools addin. To install it, just download and save the file, then activate it from within Excel.

Navigate: Office Home button -> “Excel Options” from the bottom of the menu -> Addins and Click “Go” where it says “Manage Excel Add-ins”. Click “Browse” and browse to wherever you saved the Add-in file:

how to extract url from hyperlink in excel

There are a number of functions included in the Add-in – most useful of which are:

  • subdomain() – returns the domain part of a URL including any subdomains e.g “http://www.example.com/index.htm” becomes “www.example.com”
  • wwwsubdomain() – basically, a combination of the above, equivalent to “nowww(subdomain(A1))”. This is what you want to use most of the time!
  • nowww() – removes “www.” from the start of a URL (note that www must be at the very beginning of the URL)
  • tld() – Returns the TLD of a URL (not 100% perfect but pretty good)
  • geturl() – Extracts the URL from a Hyperlink

So, getting back to our task, let’s extract domain names from the list of linked cells.

Copy and paste next to the cell you want to extract domain from (copy-paste it all other cells you want to extend the formula to):

=wwwsubdomain(cell)

Extract domain names

Do you have your own tips and tricks editing and extracting URLs in Excel? Please share them here!

  1. Flyingcows Digital Blogger
    December 22, 2010 at 6:54 pm

    Awesome! This is gonna be useful!

  2. Best Photo Book
    December 21, 2010 at 11:40 am

    If there is a better site on the web, I have not found it. Every day, I find something on the site or email digest and RSS feed that I can use. I wish all of the makeuseof people and all of their readers a joyous holiday season. John

    • Ann Smarty
      December 21, 2010 at 1:13 pm

      Thanks, John! Happy Holidays to you to!

    • Aibek
      December 22, 2010 at 10:54 am

      thank you John, happy holidays!

Leave a Reply

Your email address will not be published. Required fields are marked *