<firstimage="https://www.makeuseof.com/wp-content/uploads/2010/12/excel-urls.jpg" />
Excel 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:
- Make sure macros are enabled (in "Macro Settings")
- Make sure the addins are NOT disabled (in "Add-ins")
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:
Done!
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:
Done!
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:
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)
Do you have your own tips and tricks editing and extracting URLs in Excel? Please share them here!